How to get the integer value from varchar field?

Question

Post reply

How to get the integer value from varchar field?

sharma-691993

SSCrazy

Points: 2194
More actions
July 6, 2008 at 3:22 pm

#190235

I need to retrive the integer part from a varchar filed.
A varchar filed contains SATHIK01,SATHIK10.
How to get the integer part only (1,10) from above values.
Regards

Viewing 13 posts - 1 through 12 (of 12 total)

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

There is no easy funtion to do this, but here is a thread on the topic with a few solutions.

http://www.eggheadcafe.com/software/aspnet/32567710/replace-all-alpabet-chara.aspx

If the numbers are always at the end you can do something like this.

Declare @STR varchar(20)

Set @STR = 'SATHIK01'

select Substring(@str,PATINDEX('%[0-9]%',@str),len(@str))

Set @STR = 'SATHIK10'

select Substring(@str,PATINDEX('%[0-9]%',@str),len(@str))

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Oh, no... sorry if this sounds nasty but I wouldn't use any of those... especially (sorry to say) the "Celko" version...

Try this instead, please...

First, create a function like this...

CREATE FUNCTION dbo.udfGetNumVal

(@StrInput NVARCHAR(50))

RETURNS DECIMAL(18,0)

AS

BEGIN

--===== Return Variable

DECLARE @NumVal NVARCHAR(50)

--===== Keep only digits

SELECT @NumVal = COALESCE(@NumVal,'') + SUBSTRING(@StrInput,N,1)

FROM dbo.Tally

WHERE SUBSTRING(@StrInput,N,1) LIKE '[0-9]'

AND N <= LEN(@StrInput)

RETURN @NumVal

END

Then, use it like this...

--===== Create a demo table

-- (This is NOT part of the solution!!)

DECLARE @TestTable TABLE (SomeString VARCHAR(100))

INSERT INTO @TestTable (SomeString)

SELECT 'SATHIK01' UNION ALL

SELECT 'SATHIK10'

--===== Demo the solution

SELECT SomeString,

dbo.udfGetNumVal(SomeString)

FROM @TestTable

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 3

Good one Jeff. They will need to create the "Tally" table you have in the example.

http://www.sqlservercentral.com/articles/TSQL/62867/

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Dang... thanks for the catch, Ken... I normally list that article and plumb forgot...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

annas SSC Enthusiast Points: 121 More actions · Answer 5

Hello

This is my first post here and I hope this is not an insanely stupid question, but how does N recieve values in the function listed by Jeff?

I'm assuming that Tally in the function equates to @TestTable in the example.

Another queston is about the return data type: The function returns a decimal but the return value @numval is a character type. I have done some research in the course of trying to work this out for myself, and see that nvarchar converts implicitly to decimal but I was wondering if there was a reason that this specific conversion was chosen.

Incidentally i found that if the @NumVal is more than 18 characters it causes an arithmetic overflow error when converting to decimal(18,0). (I had to try pushing it to the limit - it's in my nature)

Thanks for your help - and I really get a lot of value from the posts on this site.

Anna

So much to learn!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

annas (7/7/2008)
Hello
This is my first post here and I hope this is not an insanely stupid question, but how does N recieve values in the function listed by Jeff?
I'm assuming that Tally in the function equates to @TestTable in the example.
Another queston is about the return data type: The function returns a decimal but the return value @numval is a character type. I have done some research in the course of trying to work this out for myself, and see that nvarchar converts implicitly to decimal but I was wondering if there was a reason that this specific conversion was chosen.
Incidentally i found that if the @NumVal is more than 18 characters it causes an arithmetic overflow error when converting to decimal(18,0). (I had to try pushing it to the limit - it's in my nature)
Thanks for your help - and I really get a lot of value from the posts on this site.
Anna
So much to learn!

Did you read the article on Tally tables? Most of the questions you ask are explained there.... and, no, @TestTable is simply the table where the test data to be split is held... the Tally table is access by the function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Also, try Decimal(38,0) or BIGINT....

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

annas SSC Enthusiast Points: 121 More actions · Answer 8

That's fantastic, I love it. It all makes sense now. I had already started converting some of the cursor code written by past DBAs but the Tally table is brilliant!

thanks

Anna

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

Thanks for the compliment, Annas... 🙂 I wish the Tally table was my original idea... but it's not... I just write about how to use it. Just in case you missed it, lots of folks refer to things like this as a "Numbers" table instead of a Tally table.

Like I've had to remind others (just to be safe), although the Tally table is very useful and replaces the need for a lot of loops, it isn't a panacea for all cursors and While loops.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

I am fairly new to the tally table... (I have used it in a couple of places modifying reference code) so I decided to play around with getting numbers from strings, as a challange. I also thought I might as well share what I came up with!

-- Get last number set

SELECT TestText

, MAX(MaxNumber.Number)

, MAX(MaxAlpha.Number)

, SUBSTRING(TestText, MAX(MaxAlpha.Number) + 1, MAX(MaxNumber.Number) - MAX(MaxAlpha.Number))

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

CROSS APPLY dbo.tNumbers MaxNumber

CROSS APPLY dbo.tNumbers MaxAlpha

WHERE MaxNumber.Number <= LEN(TestText)

AND SUBSTRING(TestText, MaxNumber.Number, 1) LIKE '[0-9]'

AND MaxAlpha.Number <= LEN(TestText)

AND SUBSTRING(TestText, MaxAlpha.Number, 1) NOT LIKE '[0-9]'

AND MaxAlpha.Number < MaxNumber.Number

GROUP BY TestText

-- Get first number set

SELECT TestText

, MIN(MinNumber.Number)

, MIN(MinAlpha.Number)

, SUBSTRING(TestText, MIN(MinNumber.Number), MIN(MinAlpha.Number) - MIN(MinNumber.Number))

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

CROSS APPLY dbo.tNumbers MinNumber

CROSS APPLY dbo.tNumbers MinAlpha

WHERE MinNumber.Number <= LEN(TestText)

AND SUBSTRING(TestText, MinNumber.Number, 1) LIKE '[0-9]'

AND MinAlpha.Number <= LEN(TestText)

AND SUBSTRING(TestText, MinAlpha.Number, 1) NOT LIKE '[0-9]'

AND MinAlpha.Number > MinNumber.Number

GROUP BY TestText

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

You might want to double check... CROSS APPLY is a fancy name for "Correlated Subquery" and can have all the same problems with performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff, wether I use the Cross Apply or an Inner Join, the Execution Plan is identical, and the performance on the Cross Apply is better (~13400 "Wait time on server replies" versus ~14400). I can look at this further later, but any thoughts as to a direction I should try (save CLR, which I realize would probably be a little easier and most likely faster). Again, this is just an exersize for me to improve my understanding of Tally tables and some more advanced "tricks" of SQL.

Thanks for the input, either way!