February 8, 2016 at 8:40 am
Hello, can you help with this.
I have list of 1000's of ID's - example below.
100||6||201203121235
100||7||201308271807
1000||1||200502050454
1000||2||201202030803
1000||3||201506152138
100001||6||200803100908
100001||23||201001290936
100001||25||201003292252
100005||4||200703311333
100005||16||201206141824
I want to find and replace so the output looks like
100-6
100-7
1000-1
1000-2
1000-3
100001-6
100001-23
100001-25
100005-4
100005-16
February 8, 2016 at 8:54 am
Anthony Ganley (2/8/2016)
Hello, can you help with this.I have list of 1000's of ID's - example below.
100||6||201203121235
100||7||201308271807
1000||1||200502050454
1000||2||201202030803
1000||3||201506152138
100001||6||200803100908
100001||23||201001290936
100001||25||201003292252
100005||4||200703311333
100005||16||201206141824
I want to find and replace so the output looks like
100-6
100-7
1000-1
1000-2
1000-3
100001-6
100001-23
100001-25
100005-4
100005-16
There are so many ways to achieve this. I'm giving you some of them. For one of them, I use a splitter function that you can find here along with it's explanation: http://www.sqlservercentral.com/articles/Tally+Table/72993/
I'm including code to generate the sample data as you should include it whenever you ask help so we don't have to spend time building the sample data.
CREATE TABLE #Sample( Somestring varchar(250));
INSERT INTO #Sample VALUES
('100||6||201203121235 '),
('100||7||201308271807 '),
('1000||1||200502050454 '),
('1000||2||201202030803 '),
('1000||3||201506152138 '),
('100001||6||200803100908 '),
('100001||23||201001290936'),
('100001||25||201003292252'),
('100005||4||200703311333 '),
('100005||16||201206141824');
SELECT PARSENAME( REPLACE(Somestring, '||', '.'), 3) + '-' + PARSENAME( REPLACE(Somestring, '||', '.'), 2)
FROM #Sample;
SELECT MAX(CASE WHEN ItemNumber = 1 THEN Item END) + '-' + MAX(CASE WHEN ItemNumber = 3 THEN Item END)
FROM #Sample
CROSS APPLY dbo.DelimitedSplit8K( Somestring, '|')
WHERE ItemNumber IN(1,3)
GROUP BY Somestring;
SELECT LEFT( ReplacedString, CHARINDEX( '-', ReplacedString, CHARINDEX('-', ReplacedString)))
FROM #Sample
CROSS APPLY (SELECT REPLACE( Somestring, '||', '-'))x(ReplacedString);
GO
DROP TABLE #Sample;
February 8, 2016 at 8:58 am
Another way (uses Luis' temp table) (thanks, Luis)
SELECT *
, replace(LEFT(Somestring, charindex('||', Somestring, CHARINDEX('||',Somestring) + 1)-1),'||','-')
FROM #Sample
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.
February 8, 2016 at 1:21 pm
Thanks guys, i'll give it a go tomorrow.
February 8, 2016 at 3:00 pm
Anthony Ganley (2/8/2016)
Thanks guys, i'll give it a go tomorrow.
If you do a lot of work with delimited columns, you'll want to spend the time to read the article that Luis posted. It isn't simple and it isn't short, but it'll change the way you look at data.
February 8, 2016 at 3:55 pm
Anthony Ganley (2/8/2016)
I have list of 1000's of ID's - example below....
On a slightly different tack, where does the ORIGINAL data in that condition come from? If it's coming in from a file, then we can fix all of this splitting stuff pretty darned easily using BULK INSERT. Lemme know.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply