Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • Last Modified:

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
0
chokka
Asked:
chokka
  • 4
  • 3
  • 2
  • +2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select left( yourfield, charindex(' ', yourfield)-1) from yourtable
0
 
chokkaAuthor Commented:
@angelIII, spills error as

Invalid length parameter passed to the LEFT or SUBSTRING function.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
David KrollCommented:
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.
0
 
Jared_SCommented:
Working off Angel's suggestion...

select left( yourfield, patindex('%[^0-9]%', yourfield)) from yourtable
0
 
Jared_SCommented:
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.
0
 
chokkaAuthor 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
0
 
Jared_SCommented:
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)
0
 
Jim P.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

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

Thank you :    left(Size,patindex('%[A-Z]%',Size)-1)
0
 
chokkaAuthor Commented:
Thank you Jared_s, Works great !!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now