Help with a query to get substring

Question

Help with a query to get substring

robert.wiglesworth

SSCommitted

Points: 1790
More actions
April 18, 2013 at 12:14 pm

#276687

Hello,
I have a column called EventText with a string such as 'Receiving batch [688_31142.TRN].' (without the ' marks)
I need to get it to be '688-31142'
I can accomplish that with this:
update #temptesttable
set
EventText = Replace(EventText, 'Receiving batch ','')
FROM
#temptesttable
update #temptesttable
set
EventText = Replace(EventText, '[','')
FROM
#temptesttable
update #temptesttable
set
EventText = Replace(EventText, '.TRN].','')
FROM
#temptesttable
update #temptesttable
set
EventText = Replace(EventText, '_','-')
FROM
#temptesttable
But there must be a much cleaner way... any suggestions?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

robert.wiglesworth (4/18/2013)
Hello,
I have a column called EventText with a string such as 'Receiving batch [688_31142.TRN].' (without the ' marks)
I need to get it to be '688-31142'
I can accomplish that with this:
update #temptesttable
set
EventText = Replace(EventText, 'Receiving batch ','')
FROM
#temptesttable
update #temptesttable
set
EventText = Replace(EventText, '[','')
FROM
#temptesttable
update #temptesttable
set
EventText = Replace(EventText, '.TRN].','')
FROM
#temptesttable
update #temptesttable
set
EventText = Replace(EventText, '_','-')
FROM
#temptesttable
But there must be a much cleaner way... any suggestions?

My question, is this a consistent format for the batch number, 688_31142.TRN (i.e. NNN_NNNNN.TRN)?

robert.wiglesworth SSCommitted Points: 1790 More actions · Answer 2

The format is somewhat consistent... What may change is the number of digits before and after the _

There will always be only numbers before and after the underscore, but there may be 3-5 digits before and 6-11 digits after.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Well you certainly don't need 4 updates for this. Not quite sure why in your original you used some many updates. This can be done a lot faster (at least 75%) by using a single update with nested replace.

update #temptesttable

set EventText = replace(replace(replace(EventText, 'Receiving batch [', ''), '.TRN].', ''), '_', '-')

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

robert.wiglesworth SSCommitted Points: 1790 More actions · Answer 4

Perfect! Thank you! I knew there had to be a way to nest the REPLACE(s). I just couldn't figure out the right order.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

robert.wiglesworth (4/18/2013)
Perfect! Thank you! I knew there had to be a way to nest the REPLACE(s). I just couldn't figure out the right order.

It really doesn't matter which order, it will work any direction because none of the replace values are dependent on each other. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 6

If you're going to have different length numbers on either side of the underscore you can use this.. it's ugly but works.

declare @textfield varchar(255)

set @textfield = 'Receiving Batch [1234_456.TRN]'

SELECT Replace(Substring(@textfield, Patindex('%[0-9]%', @textfield),

Len(@textField) - Patindex('%[0-9]%', Reverse(

@textfield)

) -

Patindex(

'%[0-9]%', @textfield) + 2), '_', '-')

.... or you can use Sean's much more elegant code. I feel like a hack today.. 😎

Alan Burstein SSC Guru Points: 61141 More actions · Answer 7

I created some test data to show a different way using the patternSplit function from here[/url].

--Setup sample data

IF OBJECT_ID('tempdb..#temptesttable') IS NOT NULL

DROP TABLE #temptesttable;

CREATE TABLE #temptesttable (EventText varchar(100));

INSERT #temptesttable

VALUES('Receiving batch [688_31142.TRN]'),

('Receiving blah [334_99133.TRN]'),

('blah blah [685_51155.TRN]');

GO

--get the data using PatternSplit

WITH NewEventText AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY EventText ORDER BY ItemNumber) AS nbr, t.EventText, xxx.*

FROM #temptesttable t

CROSS APPLY

ajbTest.dbo.PatternSplitCM(t.EventText,'%[0-9]%') xxx

WHERE Matched=1

)

SELECTx1.EventText,

x2.Item+'-'+x1.Item AS new_value

FROM NewEventText x1

JOIN NewEventText x2 ON x1.EventText=x2.EventText

AND x1.nbr=x2.nbr+1

That said, I would go with Seans solution.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001