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!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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
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]
left(Size,patindex('%[A-Z]
Premium Content
You need an Expert Office subscription to comment.Start Free Trial