Text value from a string 2

[hamzajosh]

I have a column in a table with string values like
1g
5g
25g
1kg
25kg
1l
5l
45mu and so on
Is there any function i can use to extract just the numeric value from this string. In VB, i use VAL to get the numeric value in a string. What is the equivalent in SQL. Please help thanks.

Learn everything but implement only what is needed.

 

[zhavic]

Don't know about some function like VAL() in SQL, but you can use PATINDEX for example:

SELECT LEFT( your_column, PATINDEX( '%[^0-9]%', your_column ) - 1 )
FROM your_table


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[hamzajosh]

Zhavic, awesome, worked like a charm. Would appreciate it if you could explain what the [^0-9] does in the query. Does it mean find any value greater that 0-9? thanks

Learn everything but implement only what is needed.

 

[nigelrivett]

the [] defines a single character.
0-9 means between 0 and 9
^ means not

so [^0-9] means a character not between 0 and 9.
see

http://www.nigelrivett.net/RemoveNonNumericCharacters.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[hamzajosh]

Thanks a lot, that was really helpful.

Learn everything but implement only what is needed.

 

[dbomrrsm]

I use the below udf to clean rubbish out of telephone numbers - should give you the result you require

CREATE FUNCTION udf_StripTel (@Tel varchar(1000))
RETURNS varchar(200)
AS


BEGIN
DECLARE @Cnt AS int,
@Lng AS int,
@TmpAsc as int,
@TmpStr AS varchar(1),
@ResStr AS varchar(200)

SET @Lng = len(@Tel)
SET @ResStr = ''
SET @Cnt = 1
WHILE @Cnt <= @Lng
BEGIN
SET @TmpStr = substring(@Tel,@Cnt,1)
SET @TmpAsc = isnull(AscII(@TmpStr),0)
IF (( @TmpAsc >= 48 ) AND
( @TmpAsc <= 57 ))
SET @ResStr = @ResStr + @TmpStr
SET @Cnt = @Cnt + 1
END
RETURN @ResStr
END