sql substring from column : nvarchar(255)

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

@angelIII, spills error as

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.

Working off Angel's suggestion...

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.

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

Isn't Size a keyword in SQL?

Try wrapping the field name with brackets:

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

Select all Open in new window

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

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

Thank you Jared_s, Works great !!