You must Sign In to post a response.
  • Category: SQL Server

    Split 1 column value into two columns using delimiter as Hyphen

    Hi friends,

    I need to split a single column value into 2 columns.

    ex:

    declare @cUser varchar(255)
    set @cUser='John williams - Mercury Production'

    I want to split the name and company name into 2 different columns against the delimiter Hyphen..

    I tried a solution, it gives out the result in single column multiple rows....

    Can anybody help me out to perform the above task?


    Thanks in Advance
  • #746654
    Hi,
    Try below code and see if it works as per your requirement,


    declare @cUser varchar(255)
    set @cUser='John williams - Mercury Production'

    SELECT LEFT(@cUser, PATINDEX('%-%', @cUser) - 1) as [First Name], Right(@cUser, LEN(@cUser) - PATINDEX('%-%', @cUser)) as [Last Name]


    Let me know if this doesn't work or you are looking for any other method.


    Regards,
    Asheej T K

  • #746658
    Hi,

    Use the below SQL Script to Split 1 column value into two columns.

    DECLARE @cUser VARCHAR(255)

    SET @cUser='John williams - Mercury Production'

    DECLARE @Temp TABLE
    (
    name VARCHAR(255)
    )

    INSERT @Temp
    SELECT @cUser

    SELECT PARSENAME(REPLACE(name, '-', '.'), 2) Name,
    PARSENAME(REPLACE(name, '-', '.'), 1) [Company name]
    FROM @Temp

    Regards
    Siva

  • #746669
    Hi,

    Try the below SQL Script.

    DECLARE @cUser VARCHAR(255)
    DECLARE @len INT
    DECLARE @intFlag INT
    DECLARE @RevFlag INT
    DECLARE @Query VARCHAR(5000)

    SET @intFlag = 1
    SET @cUser='John - williams - Mercury - Production'
    SET @len= ( LEN(@cUser) - LEN(REPLACE(@cUser, '-', '')) ) + 1
    SET @RevFlag=@len
    SET @Query='DECLARE @Temp TABLE (Name VARCHAR(255))'
    SET @Query+=' declare @cUser varchar(255) '
    SET @Query+=' set @cUser=''John - williams - Mercury - Production'''
    SET @Query+=' INSERT @Temp SELECT @cUser '
    SET @Query+=' SELECT '

    WHILE ( @intFlag <= @len )
    BEGIN
    IF @intFlag <> @len
    BEGIN
    SET @Query+='PARSENAME(REPLACE(Name,''-'',''.''),'
    + CAST(@RevFlag AS VARCHAR) + '),'
    END
    ELSE
    BEGIN
    SET @Query+='PARSENAME(REPLACE(Name,''-'',''.''),'
    + CAST(@RevFlag AS VARCHAR) + ')'
    END

    SET @intFlag = @intFlag + 1
    SET @RevFlag=@RevFlag - 1
    END

    SET @Query+=' FROM @Temp '

    EXEC(@Query)

    go

    Regards
    Siva

  • #746674
    Hi Lakshmi,

    This query will help you


    Declare @cUser varchar(255)
    set @cUser='John williams - Mercury Production'

    SELECT @cUser,Substring(@cUser,0,charindex('-',@cUser)) Name
    ,LTRIM(Substring(@cUser,charindex('-',@cUser)+1,LEN(@cUser)-charindex('-',@cUser))) Company

  • #747290
    split the string based on charindex and substring and insert.


  • Sign In to post your comments