November 18, 2015 at 5:21 am
I want to extract numbers from below string and insert into 2 columns respectively based on delimiter.
Banded Packs - Consumer - Buy A Get 3P - Consumer|3860701|1442421
Expected result:
Code1 Code2
3860701 1442421
Please help me with string functions.
November 18, 2015 at 5:25 am
Is there only ever 2 | in a given string
EG, will the string always be something|12345|67890
or does the number of | change in a given string and could be something like
something|12345|67890|54321|09876
If this above is possible what is the expected outcome?
November 18, 2015 at 6:59 am
It will be oly 2.
November 18, 2015 at 8:05 am
heres my quick take on it
create table #test (testdata varchar(50))
insert into #test values
('Consumer|3860701|1442421'),
('something|12345|67890')
;with cte as
(select * from #test
cross apply dbo.DelimitedSplit8K(testdata,'|')
)
select t1.item, t2.item from cte t1
inner join cte t2
on t1.testdata = t2.testdata and t1.ItemNumber = t2.ItemNumber-1
where t2.ItemNumber = 3
Note, you will need delimitedsplit8k from the string splitter link in my signature.
Others may chip in with other methods
November 18, 2015 at 8:28 am
My options:
SELECT
MAX( CASE WHEN ItemNumber = 1 THEN Item END) AS Col1,
MAX( CASE WHEN ItemNumber = 2 THEN Item END) AS Col2,
MAX( CASE WHEN ItemNumber = 3 THEN Item END) AS Col3
FROM #test
CROSS APPLY dbo.DelimitedSplit8K(testdata,'|')
GROUP BY testdata;
SELECT
LEFT( testdata, x.delimiter1 - 1) AS Col1,
SUBSTRING( testdata, x.delimiter1 + 1, y.delimiter2 - x.delimiter1 - 1) AS Col2,
SUBSTRING( testdata, y.delimiter2 + 1, 50) AS Col3
FROM #test
CROSS APPLY (SELECT CHARINDEX( '|', testdata + '|') delimiter1)x
CROSS APPLY (SELECT CHARINDEX( '|', testdata + '|', delimiter1 + 1) delimiter2)y;
SELECT --Don't use this if there are periods in your data
PARSENAME( REPLACE( testdata, '|', '.'), 3) AS Col1,
PARSENAME( REPLACE( testdata, '|', '.'), 2) AS Col2,
PARSENAME( REPLACE( testdata, '|', '.'), 1) AS Col3
FROM #test
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply