Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Getting the Last Integer values Expand / Collapse
Author
Message
Posted Monday, July 7, 2008 7:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 1:56 AM
Points: 438, 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.


---
Post #529292
Posted Monday, July 7, 2008 7:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 40,187, Visits: 36,593
Do you just want the last 2 characters? If so...

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




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #529321
Posted Monday, July 7, 2008 8:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 6,788, Visits: 13,999
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
Exploring Recursive CTEs by Example Dwain Camps
Post #529322
Posted Monday, July 7, 2008 8:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 31, 2010 1:28 AM
Points: 141, Visits: 263
Hi,
i have a doubt whether the length of this string('Te6st03') will change or its static.

Thanks.
Post #529326
Posted Monday, July 7, 2008 10:55 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 1:56 AM
Points: 438, Visits: 754
Hi guys,

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


---
Post #529755
Posted Tuesday, July 8, 2008 12:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 40,187, Visits: 36,593
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 2008, MVP
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

Post #529775
Posted Tuesday, July 8, 2008 12:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 1:56 AM
Points: 438, 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


Post #529781
Posted Tuesday, July 8, 2008 4:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 31, 2010 1:28 AM
Points: 141, 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)

Post #529860
Posted Tuesday, July 8, 2008 4:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 6,788, Visits: 13,999
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
Post #529890
Posted Tuesday, July 8, 2008 5:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,220, Visits: 2,614
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
Post #529901
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse