September 8, 2021 at 12:43 pm
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:31 pm
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://qa.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function
September 8, 2021 at 1:35 pm
+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.
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
September 8, 2021 at 1:47 pm
--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
September 8, 2021 at 3:17 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply