CHARINDEX

Question

CHARINDEX

Ganners

SSChasing Mays

Points: 636
More actions
February 8, 2016 at 8:40 am

#399685

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

Another way (uses Luis' temp table) (thanks, Luis)

SELECT *

, replace(LEFT(Somestring, charindex('||', Somestring, CHARINDEX('||',Somestring) + 1)-1),'||','-')

FROM #Sample

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

Ganners SSChasing Mays Points: 636 More actions · Answer 3

Ganners

SSChasing Mays

Points: 636

February 8, 2016 at 1:21 pm

#1857231

Thanks guys, i'll give it a go tomorrow.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)