Click here to Skip to main content
12,405,700 members (60,780 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

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

Solution 1

  Permalink  
v2
Comments
Maciej Los 9-Apr-13 1:48am
   
+5!
Aarti Meswania 9-Apr-13 1:49am
   
Thank you! :)
Rate this: bad
 
good
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)
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
  Permalink  

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
Web02 | 2.8.160721.1 | Last Updated 5 Apr 2013
Copyright © CodeProject, 1999-2016
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