sql substring from column : nvarchar(255)

chokka
chokka used Ask the Experts™
on
I have a column with datatype nvarchar(255)

Size

30 EA
2.5 ML
100 EA
60 EA
100 EA
30 EA
90 EA
60 EA
30 EA
8.5 GM

I need to pick only numerical value


Size

30
2.5
100
60
100
30
90
60
30
8.5
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
select left( yourfield, charindex(' ', yourfield)-1) from yourtable
chokkaStudent

Author

Commented:
@angelIII, spills error as

Invalid length parameter passed to the LEFT or SUBSTRING function.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

if you get the invalid length from this:

select left( yourfield, charindex(' ', yourfield)-1) from yourtable

It means the field doesn't have a space in it, that's what it's looking for.
Top Expert 2012

Commented:
Working off Angel's suggestion...

select left( yourfield, patindex('%[^0-9]%', yourfield)) from yourtable
Top Expert 2012

Commented:
If this field is an nvarchar(255), then it's either bigger than it needs to be, or you're storing more than one value in the field.

If it's the latter, then you'll need a user defined function to strip the characters out of the entire string. dkrollCTN posted a link to handle that scenario.
chokkaStudent

Author

Commented:
Size

100 EA
14.7 GM

Three different Query

select left( Size, patindex('%[^0-9]%', Size)-1)
from PlacedOrderDetails

Output

Size

100
14

select left( Size, patindex('%[^0-9]%', Size))
from PlacedOrderDetails

Size

100
14.


select left( Size, patindex('%[^0-9]%', Size)+1)
from PlacedOrderDetails

Size

100 E
14.7

In my scenario, i expect the output as ..

Size

100
14.7
Top Expert 2012
Commented:
The above query looks for the first non-numeric value (which is the decimal). This should get you around it by looking for the first alpha character instead.

left(Size,patindex('%[A-Z]%',Size)-1)
Most Valuable Expert 2014

Commented:
Isn't Size a keyword in SQL?

Try wrapping the field name with brackets:

select left( [Size], patindex('%[^0-9]%', [Size]))
from PlacedOrderDetails

Open in new window

chokkaStudent

Author

Commented:
@Jared_S , Thank you !! Your syntax is working.

Thank you :    left(Size,patindex('%[A-Z]%',Size)-1)
chokkaStudent

Author

Commented:
Thank you Jared_s, Works great !!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial