Query Help....

Question

Post reply

Query Help....

sqldba20

Ten Centuries

Points: 1308
More actions
February 27, 2012 at 9:05 am

#265237

Folks:
I have a column which stores data in this format:
TR#ABC#10/12/2011
TR#XYZ#12/12/2011
TR#ABC#10/12/2011
I would like to remove the text before and after #. The output should be:
ABC
XYZ
ABC
Thanks !

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Something like this?

SELECT *,

CHARINDEX('#', C),

CHARINDEX('#', C, CHARINDEX('#', C) + 1),

SUBSTRING(C, CHARINDEX('#', C) + 1, CHARINDEX('#', C, CHARINDEX('#', C) + 1) - CHARINDEX('#', C) - 1)

FROM ( VALUES ( 'TR#ABC#10/12/2011'), ( 'TR#XYZ#12/12/2011'), ( 'TR#ABC#10/12/2011') ) S (C)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

charindex (http://msdn.microsoft.com/en-us/library/ms186323.aspx) or patindex (http://msdn.microsoft.com/en-us/library/ms188395.aspx) work well for finding places of patterns in a string.

Then substring (http://msdn.microsoft.com/en-us/library/ms187748.aspx), as Gus as done, to strip out items.

Durai Samuel Mr or Mrs. 500 Points: 551 More actions · Answer 3

Can you try this

CREATE TABLE #Temp(Word VARCHAR(100))

INSERT INTO #Temp VALUES('TR#ABC#10/12/2011')

INSERT INTO #Temp VALUES('TR#XYZ#12/12/2011')

INSERT INTO #Temp VALUES('TR#ABC#10/12/2011')

SELECT REPLACE(MyWords, LEFT(MyWords, CHARINDEX('#', MyWords)), '') AS Word FROM

(

SELECT REPLACE(Word, RIGHT(Word, CHARINDEX('#', REVERSE(Word))), '') AS MyWords FROM #Temp

) A

DROP TABLE #Temp

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

gpm.alwyn (2/27/2012)
Can you try this
CREATE TABLE #Temp(Word VARCHAR(100))
INSERT INTO #Temp VALUES('TR#ABC#10/12/2011')
INSERT INTO #Temp VALUES('TR#XYZ#12/12/2011')
INSERT INTO #Temp VALUES('TR#ABC#10/12/2011')
SELECT REPLACE(MyWords, LEFT(MyWords, CHARINDEX('#', MyWords)), '') AS Word FROM
(
SELECT REPLACE(Word, RIGHT(Word, CHARINDEX('#', REVERSE(Word))), '') AS MyWords FROM #Temp
) A
DROP TABLE #Temp

What is wrong with the one Gus posted?

select SUBSTRING(Word, CHARINDEX('#', Word) + 1, CHARINDEX('#', Word, CHARINDEX('#', Word) + 1) - CHARINDEX('#', Word) - 1) from #Temp

I would say Gus's is easier to read and faster because it doesn't have to reverse.

_______________________________________________________________

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/