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: 1113
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: 113507 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: 246985 More actions · Answer 2

+1 for using a splitter to do this.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Ant-Green SSC Guru Points: 113507 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: 101120 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".