Select only the numeric from an alphanumeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select only the numeric from an alphanumeric

Author

Topic

MrBloom
Starting Member

36 Posts

Posted - 2014-09-29 : 07:19:14

Hi

I have a series of alphanumeric values where the first character is always a letter A-F and the following characters are always numerics. I would like to select only the number part of the alphanumeric which can be one or two digits long.
Do you know the correct function for this?
ie.

A9 = 9
A10 = 10

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-29 : 07:49:34

select SUBSTRING(column1,2,(len(column1)-1))

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

MrBloom
Starting Member

36 Posts

Posted - 2014-09-29 : 07:55:35

Thank you very much !

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-09-29 : 13:41:50

Or, to allow for any length of non-numeric prefix:


SELECT SUBSTRING(string, PATINDEX('[%0-9%]', string), 100) AS numeric_string
FROM (
    SELECT 'A9' AS string UNION ALL
    SELECT 'A10' UNION ALL
    SELECT 'AA555' UNION ALL
    SELECT 'AAAA11111'
) AS test_data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources