Selecting number values from a string column up to first non-numeric chars

Question

Selecting number values from a string column up to first non-numeric chars

mmiruru

SSC Rookie

Points: 44
More actions
December 10, 2008 at 10:33 am

#126403

How do i select numeric values up to the first non-numeric char from a table column that contains alpha numeric strings? For example;
24K-20k to return 24
200-300 to return 200
5k to return 5 etc.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Rob-301975 SSC Enthusiast Points: 198 More actions · Answer 1

I would suggest looking into Regular expressions. I am not proficient in how to use them but I'm sure it will be able to do what you're looking for.

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 2

I don't know how you'd do it in SSIS, but here's the T-SQL solution...

--===== Create a place to hold test data and populate it.

-- Note that this is NOT part of the solution.

DECLARE @DemoTable TABLE (Original VARCHAR(10))

INSERT INTO @DemoTable

SELECT '24K-20k' UNION ALL

SELECT '200-300' UNION ALL

SELECT '5k'

--===== Solve the problem

SELECT Original,

LEFT(Original, PATINDEX('%[^0-9]%',Original)-1) AS FirstNumeric

FROM @DemoTable

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 3

Heh... I hate it when impatient people pull this crap... this is a double post... please, no more answers on this thread... go to the following, instead...

http://www.sqlservercentral.com/Forums/Post.aspx?SessionID=gjq2tn3letp4qj554vsxeizy

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)