Getting the Last Integer values

Question

Post reply

Getting the Last Integer values

SqlUser-529296

SSCarpal Tunnel

Points: 4072
More actions
July 7, 2008 at 7:37 am

#187802

Hi all,
DECLARE @T nvarchar(16)
SET @T = 'Te6st03'
From the above statement, i want the result as "03" instead of "6st03".
How can i achieve this within the single statement.
---

Viewing 15 posts - 1 through 15 (of 15 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Do you just want the last 2 characters? If so...

SELECT @T = RIGHT('Te6st03',2)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

A single statement?

SET @T = '03' 😉

Or perhaps

SET @T = REVERSE(LEFT(REVERSE(@T),2))

Or even

SET @T = substring(@T, 6, 2)

All of these (and Gail's solution) will give the result "03". Is there something more to your problem?

^{“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

Chandru -734144 Ten Centuries Points: 1040 More actions · Answer 3

Hi,

i have a doubt whether the length of this string('Te6st03') will change or its static.

Thanks.

SqlUser-529296 SSCarpal Tunnel Points: 4072 More actions · Answer 4

Hi guys,

@T should be changeable one. It ends with '03' or '003' etc...

I think that is the stuff here. 🙂

---

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

How about you give us the entire requirement with several sample values and the results you want out?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SqlUser-529296 SSCarpal Tunnel Points: 4072 More actions · Answer 6

hI GilaMonster,

Below is my requirement.

declare @t nvarchar(16)

set @t = 'T5e3st003'

from this, i want to extract only '003'. For example in a variable say may be lot of integer value be there. from this i have to extract the last integer value.

declare @t nvarchar(16),@w nvarchar(16)

set @t = 'T5e3st003' (Note: in this 5 and 3 are other integer values. in result set it should not be display.)

set @w = 'Test001'

result

-----

@t = 003

@w = 001

Chandru -734144 Ten Centuries Points: 1040 More actions · Answer 7

Hi.,

Hope im correct jus try this and some may come out with an optimized Query.

DECLARE @NumStr varchar(1000)

declare @num int

SET @NumStr = 'T5e3st003';

set @num=(select PATINDEX('%[A-Z]%[A-Z]%[A-Z]%',reverse(@NumStr)))

BEGIN

WHILE PATINDEX('%[^0-9]%',@NumStr)> 0

SET @NumStr = REPLACE(@NumStr,SUBSTRING(@NumStr,PATINDEX('%[^0-9]%',@NumStr),1),'')

END

select right(@NumStr,@num-1)

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

DECLARE @t nvarchar(16)

SET @t = 'T5e3st003'

SELECT RIGHT(@t, MIN(number)-1)

FROM Numbers

WHERE SUBSTRING(REVERSE(@t), number, 1) NOT IN ('1','2','3','4','5','6','7','8','9','0')

AND number < 10

DROP TABLE #test

CREATE TABLE #test (t nvarchar(16))

INSERT INTO #test (t)

SELECT 'T5e3st003' UNION ALL SELECT 'Test001'

SELECT RIGHT(t, MIN(number)-1)

FROM Numbers, #test

WHERE SUBSTRING(REVERSE(t), number, 1) NOT IN ('1','2','3','4','5','6','7','8','9','0')

AND number < 10

GROUP BY t

^{“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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 9

I think this will do whay you want.

Declare @STR varchar(20)

Set @STR = 'T3es5ta12p001'

select Substring(@str,len(@str)-PATINDEX('%[A-Z]%',reverse(@str))+2,len(@str))

Chandru -734144 Ten Centuries Points: 1040 More actions · Answer 10

Ken it works well ..

Good Job.

Thanks for posting the Query.

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 11

Not sure on performance, but just for fun, here is what I came up with using a tally table...

SELECT TestText,MAX(n.Number), SUBSTRING(TestText, MAX(n.Number)+1, LEN(TestText) - MAX(n.Number))

FROM (SELECT 'T5e3st003' AS 'TestText') AS a

CROSS APPLY dbo.tNumbers n

WHERE n.Number <= LEN(TestText)

AND ISNUMERIC(SUBSTRING(TestText, n.Number, 1)) < 1

GROUP BY TestText

SqlUser-529296 SSCarpal Tunnel Points: 4072 More actions · Answer 12

Hi Guys,

Thanks you very much for your kind posting. All the post statements are working fine and that are valuable for me. Good Work Guys. 🙂

SqlUser-529296 SSCarpal Tunnel Points: 4072 More actions · Answer 13

Ken Simmons (7/8/2008)
I think this will do whay you want.
Declare @STR varchar(20)
Set @STR = 'T3es5ta12p001'
select Substring(@str,len(@str)-PATINDEX('%[A-Z]%',reverse(@str))+2,len(@str))

Hi Ken Simmons,

I want to know why you use (+2) in your context?

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 14

Let me know if this makes any sense.

Declare @STR varchar(20)

Set @STR = 'T3es5ta12p001'

--This is what we are trying to duplicate

select Substring(@str,11,3)

Select len(@str) LengthOfString,

reverse(@str) StringReversed,

PATINDEX('%[A-Z]%',reverse(@str)) IndexOfFirstNonNumericCharacterFromTheEnd,

len(@str)-PATINDEX('%[A-Z]%',reverse(@str)) IndexFromTheBeginning

--Add 1 to get past the p

--Add 1 because substring takes the nth character, not the first character past n

select Substring(@str,len(@str)-PATINDEX('%[A-Z]%',reverse(@str))+2,len(@str))