Avatar of chokka
chokka
Flag for United States of America asked on

sql substring from column : nvarchar(255)

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
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
chokka

8/22/2022 - Mon
David Kroll

Guy Hengel [angelIII / a3]

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

ASKER
@angelIII, spills error as

Invalid length parameter passed to the LEFT or SUBSTRING function.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
David Kroll

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.
Jared_S

Working off Angel's suggestion...

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

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
chokka

ASKER
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
ASKER CERTIFIED SOLUTION
Jared_S

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jim P.

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

chokka

ASKER
@Jared_S , Thank you !! Your syntax is working.

Thank you :    left(Size,patindex('%[A-Z]%',Size)-1)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
chokka

ASKER
Thank you Jared_s, Works great !!