Click here to Skip to main content
13,054,615 members (63,586 online)
Rate this:
Please Sign up or sign in to vote.
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

Posted 4-Apr-13 23:45pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Maciej Los 9-Apr-13 1:48am
Aarti Meswania 9-Apr-13 1:49am
Thank you! :)
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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

DECLARE @tmp TABLE (someText VARCHAR(30))
INSERT INTO @tmp (someText)
INSERT INTO @tmp (someText)
INSERT INTO @tmp (someText)
SELECT someText, CONVERT(INT, SUBSTRING(someText, StartPos, EndPos)) AS MyNumeric
	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

someT.. MyNumeric
6361	6361
AA6063	6063
RDE40	40

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.170713.1 | Last Updated 5 Apr 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100