SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting the Last Integer values


Getting the Last Integer values

Author
Message
SqlUser-529296
SqlUser-529296
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1242 Visits: 754
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.


---
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226615 Visits: 46330
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
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41813 Visits: 20007
A single statement?

SET @T = '03' Wink

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
Exploring Recursive CTEs by Example Dwain Camps
Chandru -734144
Chandru -734144
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 263
Hi,
i have a doubt whether the length of this string('Te6st03') will change or its static.

Thanks.
SqlUser-529296
SqlUser-529296
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1242 Visits: 754
Hi guys,

@T should be changeable one. It ends with '03' or '003' etc...
I think that is the stuff here. Smile


---
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226615 Visits: 46330
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
SqlUser-529296
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1242 Visits: 754
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
Chandru -734144
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 263
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
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41813 Visits: 20007
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
Exploring Recursive CTEs by Example Dwain Camps
KenSimmons
KenSimmons
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2562 Visits: 2614
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))

Ken Simmons
http://twitter.com/KenSimmons
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search