get lastname,firstname, middlename as first 3 pipe delimiter last data no need

  • NAZARE|DEV|T||Current~NAZARE|DEV|T||Previous

    The above data NAZARE =Lastname , Dev=Firstname T= lastname .

    Could you please help me to writ in select statement using substring and charindex Pipe delimiter

  • Show what you have done so far and what you are stuck on, what works, what doesn't work etc.


    Also why the requirement to use substring and charindex, it would be better to use a string splitting function instead like

  • +1 for using a splitter to do this.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • --Using the spliter I mentioned
    declare @stringtosplit varchar(100) = 'NAZARE|DEV|T||Current~NAZARE|DEV|T||Previous'
    max(case when itemnumber = 1 then Item end) as FirstValue
    ,max(case when itemnumber = 2 then Item end) as SecondValue
    ,max(case when itemnumber = 3 then Item end) as ThirdValue
    from mydb.dbo.DelimitedSplit8K (@stringtosplit,'|')

    VS charindex just to get the locations of the first 3 pipes

    declare @stringtosplit varchar(100) = 'NAZARE|DEV|T||Current~NAZARE|DEV|T||Previous'
    charindex('|',@stringtosplit,1),--FIRST |
    charindex('|',@stringtosplit,charindex('|',@stringtosplit,1)+1), --SECOND |
    charindex('|',@stringtosplit,charindex('|',@stringtosplit,charindex('|',@stringtosplit,1)+1)+1) -- THIRD |

    You can easily read the function code vs having to pump all that gubbins from charindex into substring and you just get lost.

    Splitting on a delimiter use a function

  • To me, 50/50 on using a splitter for just the first 3.  Here's code using CHARINDEX & SUBSTRING:

    DECLARE @string varchar(200)

    SET @string = 'NAZARE|DEV|T||Current~NAZARE|DEV|T||Previous'

    SELECT string, Lastname, Firstname, Middlename
    FROM ( SELECT @string AS string ) AS data
    SELECT LEFT(string, CHARINDEX('|', string) - 1) AS Lastname,
    SUBSTRING(string, CHARINDEX('|', string) + 1, 8000) AS Remainder1
    ) AS ca1
    SELECT LEFT(Remainder1, CHARINDEX('|', Remainder1) - 1) AS Firstname,
    SUBSTRING(Remainder1, CHARINDEX('|', Remainder1) + 1, 8000) AS Remainder2
    ) AS ca2
    SELECT LEFT(Remainder2, CHARINDEX('|', Remainder2 + '|') - 1) AS Middlename
    ) AS ca3

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply