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

Question

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

DBA.k

Ten Centuries

Points: 1105
More actions
September 8, 2021 at 12:43 pm

#3927837

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
September 8, 2021 at 1:37 pm

This was removed by the editor as SPAM
September 8, 2021 at 1:37 pm

This was removed by the editor as SPAM
September 8, 2021 at 1:38 pm

This was removed by the editor as SPAM

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

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

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

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 https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function

Phil Parkin SSC Guru Points: 246695 More actions · Answer 2

+1 for using a splitter to do this.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

--Using the spliter I mentioned
declare @stringtosplit varchar(100) = 'NAZARE|DEV|T||Current~NAZARE|DEV|T||Previous'
select 
 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'
select 
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

ScottPletcher SSC Guru Points: 100948 More actions · Answer 4

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
CROSS APPLY (
    SELECT LEFT(string, CHARINDEX('|', string) - 1) AS Lastname, 
        SUBSTRING(string, CHARINDEX('|', string) + 1, 8000) AS Remainder1
) AS ca1
CROSS APPLY (
    SELECT LEFT(Remainder1, CHARINDEX('|', Remainder1) - 1) AS Firstname, 
        SUBSTRING(Remainder1, CHARINDEX('|', Remainder1) + 1, 8000) AS Remainder2
) AS ca2
CROSS APPLY (
    SELECT LEFT(Remainder2, CHARINDEX('|', Remainder2 + '|') - 1) AS Middlename
) AS ca3

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.