May 26, 2015 at 5:12 am
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.
____________________________________________________________
APMay 26, 2015 at 11:53 pm
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
May 27, 2015 at 2:31 am
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),'','')
____________________________________________________________
APMay 27, 2015 at 5:30 am
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
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
May 27, 2015 at 5:46 am
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;
May 27, 2015 at 6:44 am
Thanks a lot for your help Ed and Chris. It works.
Cheers!..
____________________________________________________________
APViewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply