Click here to Skip to main content
11,570,785 members (53,301 online)
Rate this: bad
good
Please Sign up or sign in to vote.
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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
v2
Comments
Maciej Los at 9-Apr-13 1:48am
   
+5!
Aarti Meswania at 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


Advertise | Privacy | Mobile
Web04 | 2.8.150624.2 | Last Updated 5 Apr 2013
Copyright © CodeProject, 1999-2015
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