Extracting Number from Right Side of Text

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extracting Number from Right Side of Text

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-06-05 : 10:00:23

There is a table TestTable with a UnitOfMeasure column that may contain:

case1
case
case12
case32
case6

I need to extract the rightmost number, and if there is no digit, have it default to a digit of "1", and put it in a column called ConversionFactor. I need to do this in a SELECT statement (not a stored procedure or function), in the form:

SELECT UnitOfMeasure, XXXXXXXXXXXXXXXX As ConversionFactor
FROM TestTable

So the query would yield:

UnitOfMeasure|ConversionFactor
case1........|...............1
case.........|...............1
case12.......|..............12
case32.......|..............32
case6........|...............6

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-05 : 10:11:23

reverse, grab all digits, reverse. if no digits, make it 1.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 10:19:31

if its always case as string you can use like


SELECT CAST(CASE WHEN REPLACE(field,'Case','') > '' THEN REPLACE(field,'Case','') ELSE 1 END AS int) AS Digit FROM table

if string is not consistent use

SELECT CASE WHEN STUFF(Field,1,PATINDEX('%[0-9]%',Field)-1,'') > '' THEN STUFF(Field,1,PATINDEX('%[0-9]%',Field)-1,'') ELSE 1 END FROM table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-06-05 : 10:37:49

Unfortunately, your answer is too general for me to understand. This is the "Beginning SQL" forum, and if I understood what you mean I probably wouldn't need to ask the question. It's as if I asked you how to bake a cake, and you replied "Mix flour, eggs, sugar in a bowl and bake". Yes, I would understand in general how to bake a cake, but I still wouldn't be able to actually bake one.

quote:
Originally posted by DonAtWork

reverse, grab all digits, reverse. if no digits, make it 1.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-06-05 : 10:53:41

Yes, it can be anything, so I used your second example, and it worked! Thanks so much, searched everywhere and couldn't find a solution - any solution was using a function or stored procedure to do it in a very bloated fashion.

quote:
Originally posted by visakh16

if its always case as string you can use like

SELECT CAST(CASE WHEN REPLACE(field,'Case','') > '' THEN REPLACE(field,'Case','') ELSE 1 END AS int) AS Digit FROM table

if string is not consistent use

SELECT CASE WHEN STUFF(Field,1,PATINDEX('%[0-9]%',Field)-1,'') > '' THEN STUFF(Field,1,PATINDEX('%[0-9]%',Field)-1,'') ELSE 1 END FROM table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 12:31:17

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-06 : 16:05:20

One more way







create table #tab ( name varchar(20))

insert into #tab(name)
select 'case1'
union all
select 'case'
union all
select 'star12'
union all
select 'ababab11111'
union all
select 'vijay2899022'



select name,case when substring(name,(patindex('%[0-9]%',name)),len(name)-(patindex('%[0-9]%',name))+1)=name 
then 1 else substring(name,(patindex('%[0-9]%',name)),len(name)-(patindex('%[0-9]%',name))+1) end 
from #tab

Vijay is here to learn something from you guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources