Using string functions on Text

Question

Using string functions on Text

SQL.AP

Hall of Fame

Points: 3244
More actions
May 26, 2015 at 5:12 am

#308510

Hi,
I've below value in a column with data type - TEXT
QU 221025U2V/AN G-DT DL A 5 1A- 11,5,SF,230,30162,LZ,2,118,0,0,10170,25,06
This text value has some special characters in it. and I could not paste the exact value as this text box is not allowing me to do so. So, for reference I've attached a screenshot (Capture.png) of the value.
I want to fetch last two values from this text i.e. 25 and 06. (It can be anything like 56R,06T but will be the last two values separated by comma)
Please help.
____________________________________________________________
AP

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

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

Anshul.P (5/26/2015)
Hi,
I've below value in a column with data type - TEXT
QU 221025U2V/AN G-DT DL A 5 1A- 11,5,SF,230,30162,LZ,2,118,0,0,10170,25,06
This text value has some special characters in it. and I could not paste the exact value as this text box is not allowing me to do so. So, for reference I've attached a screenshot (Capture.png) of the value.
I want to fetch last two values from this text i.e. 25 and 06. (It can be anything like 56R,06T but will be the last two values separated by comma)
Please help.

Will there always be a special character at the end of the normal characters?

--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)

SQL.AP Hall of Fame Points: 3244 More actions · Answer 2

Yes, there will always be a special character in the end.

I've tried something as below, it works, but with certain values.

DECLARE @txtMessageText varchar(max)

set @txtMessageText = 'QU - 119480,8377,EX,0810,4300,LT,3,154,1,0,15005,08R,17'

SELECT

REPLACE(SUBSTRING(CAST(@txtMessageText as varchar(max)),LEN(REVERSE(CAST(@txtMessageText as varchar(max))))-CHARINDEX(',',REVERSE(CAST(@txtMessageText as varchar(max))),CHARINDEX(',',REVERSE(CAST(@txtMessageText as varchar(max))))+2)+2,4),',','')

,REPLACE(SUBSTRING(CAST(@txtMessageText as varchar(max)),LEN(REVERSE(CAST(@txtMessageText as varchar(max))))-CHARINDEX(',',REVERSE(CAST(@txtMessageText as varchar(max))))+2,4),'','')

____________________________________________________________

AP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

DECLARE @txtMessageText VARCHAR(MAX)

SET @txtMessageText = 'QU - 119480,8377,EX,0810,4300,LT,3,154,1,0,15005,08R,17'

-- NOTE: STUFF() removes the final character from the string. Since the string is

-- reversed, the expression is STUFF(expression,1,1,'')

SELECT vcTxet,

[Element_n-1] = REVERSE(SUBSTRING(x.vcTxet,y.FirstComma+1,y.SecondComma-y.FirstComma-1)),

[Element_n] = REVERSE(STUFF(SUBSTRING(x.vcTxet,1,y.FirstComma-1),1,1,''))

FROM (SELECT MessageText = @txtMessageText) d -- < this is a one-row mockup of your table

CROSS APPLY ( -- cast to VC and reverse

SELECT vcTxet = REVERSE(CAST(d.MessageText AS VARCHAR(MAX)))

) x

CROSS APPLY ( -- calculate the comma positions in the reversed VC string

SELECT

FirstComma = CHARINDEX(',',x.vcTxet,1),

SecondComma = CHARINDEX(',',x.vcTxet,CHARINDEX(',',x.vcTxet,1)+1)

) y

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Here's another approach that eliminates the REVERSE, but uses Jeff's DelimitedSplit8K function to pull the last two items from the list. Since you say it'll always be there but you don't want it, the CTE pulls off the last character from the string before splitting it.

DECLARE @strText VARCHAR(8000);

SET @strText = 'QU - 119480,8377,EX,0810,4300,LT,3,154,1,0,15005,08R,17';

WITH cteData AS (

SELECT SUBSTRING(@strText, 1, LEN(@strText) - 1) DelimitedString

)

SELECT TOP 2 s.itemnumber, s.item

FROM cteData d

CROSS APPLY dbo.DelimitedSplit8K(d.DelimitedString, ',') s

ORDER BY s.ItemNumber DESC;

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

SQL.AP Hall of Fame Points: 3244 More actions · Answer 5

Thanks a lot for your help Ed and Chris. It works.

Cheers!..

____________________________________________________________

AP