chokka
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
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
http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/
select left( yourfield, charindex(' ', yourfield)-1) from yourtable
ASKER
@angelIII, spills error as
Invalid length parameter passed to the LEFT or SUBSTRING function.
Invalid length parameter passed to the LEFT or SUBSTRING function.
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.
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.
Working off Angel's suggestion...
select left( yourfield, patindex('%[^0-9]%', yourfield)) from yourtable
select left( yourfield, patindex('%[^0-9]%', yourfield)) from yourtable
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.
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Isn't Size a keyword in SQL?
Try wrapping the field name with brackets:
Try wrapping the field name with brackets:
select left( [Size], patindex('%[^0-9]%', [Size]))
from PlacedOrderDetails
ASKER
@Jared_S , Thank you !! Your syntax is working.
Thank you : left(Size,patindex('%[A-Z] %',Size)-1 )
Thank you : left(Size,patindex('%[A-Z]
ASKER
Thank you Jared_s, Works great !!