Getting the Integer Values from Text in Sql server 2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi All,

I have a column in a table which has the values in three combinations

i)Integer values(e.g 6361)
2)Integer values followed by two alphabets (e.g AA6063)
3)Integer values followed by three alphabets (e.g RDE40)

Now i need to get only the integer values in sql query.

I want the results for the above mentioned examples as
i) 6361
ii) 6063
iii) 40

How to get it in Sql Server 2008

Thanks...

Posted 4-Apr-13 23:45pm

Member 8445821

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Accepted Answer · 2013-04-04T23:49:00

Solution 1

visit link for solution
SQL SERVER – Get Numeric Value From Alpha Numeric String – UDF for Get Numeric Numbers Only[^]

Happy Coding!
:)

Posted 4-Apr-13 23:49pm

Aarti Meswania

Updated 4-Apr-13 23:50pm

v2

Comments

Maciej Los 9-Apr-13 1:48am

+5!

Aarti Meswania 9-Apr-13 1:49am

Thank you! :)

Maciej Los · Accepted Answer · 2013-04-05T00:09:00

Solution1 is very good, but uses loops. Alternatively, you can try something like this:

SQL

DECLARE @tmp TABLE (someText VARCHAR(30))

INSERT INTO @tmp (someText)
VALUES('6361')
INSERT INTO @tmp (someText)
VALUES('AA6063')
INSERT INTO @tmp (someText)
VALUES('RDE40')
 
SELECT someText, CONVERT(INT, SUBSTRING(someText, StartPos, EndPos)) AS MyNumeric
FROM(
	SELECT someText, PATINDEX('%[0-9]%',someText) AS StartPos, CASE
			WHEN PATINDEX('%[A-Za-z]%',SUBSTRING(someText, PATINDEX('%[0-9]%',someText) +1, LEN(someText)-PATINDEX('%[0-9]%',someText)))=0 THEN LEN(someText) 
			ELSE PATINDEX('%[A-Za-z]%',SUBSTRING(someText, PATINDEX('%[0-9]%',someText) +1, LEN(someText)-PATINDEX('%[0-9]%',someText)))
			END AS EndPos
FROM @tmp) AS T

Result:

someT.. MyNumeric
6361	6361
AA6063	6063
RDE40	40