Select numeric value from text string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select numeric value from text string

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-01-22 : 12:20:58

Hi, all,

I need a little help to get the numberic value out from the text strings as following sample data has indicated.

Note: this is not a formating issue, which we all know we should leave it to the front end app. Rather, this is for an ETL process to compare the data different from two different sources.

Thanks!

Declare @Var1 as varchar(50)
Declare @Var2 as varchar(50)
Declare @Var3 as varchar(50)
Declare @Var4 as varchar(50)
Set @Var1 = '0000000.00' --to show as 0
Set @Var2 = '0000149.00' --to show as 149
Set @Var3 = '0000008.10' --to show as 8.1
Set @Var4 = '0000010.12' --to show as 10.12

I have tried several variasions of the next two ways, but haven't got it all figured out.

Select cast (@Var1 as decimal(4,2)) --got .00
Select stuff (@Var2, 1, patindex ('%[^0]%', @Var2) -1, '') --got 149.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-22 : 12:37:36

CASE WHEN CAST(SUBSTRING(@Var,CHARINDEX('.',@Var)+1,2)) AS int)> 0
THEN CAST(@Var AS decimal(4,2))
ELSE CAST(@Var AS decimal(4,0))
END

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-01-22 : 13:21:59

Thank you for the reply.

Your solution did not remove the 0 at the end of decimal place, and the decimal point if nothing but zeros come after it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 13:37:27

[code]-- Prepare sample data
DECLARE @Sample TABLE (txt VARCHAR(50))

INSERT @Sample
SELECT '0000000.00' UNION ALL
SELECT '0000149.00' UNION ALL
SELECT '0000008.10' UNION ALL
SELECT '0000010.12'

-- Show the expected output
SELECT txt AS txt1,
CASE
WHEN txt2 = '.' THEN '0'
WHEN RIGHT(txt2, 1) = '.' THEN LEFT(txt2, LEN(txt2) - 1)
ELSE txt2
END AS txt2
FROM (
SELECT REPLACE(LTRIM(RTRIM(REPLACE(txt, '0', ' '))), ' ', '0') AS txt2,
txt
FROM @Sample
) AS Yak[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 13:38:19

Output from above query is

txt1		txt2
0000000.00	0
0000149.00	149
0000008.10	8.1
0000010.12	10.12

E 12°55'05.25"
N 56°04'39.16"

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-01-22 : 13:41:13

I also figured it out by modifying on visakh16's post as follow:

Case WHEN SUBSTRING(@Var1,CHARINDEX('.',@Var1)+1,2) = '00' then
ISNULL(stuff (LEFT(@Var1, len(@Var1)-3), 1, patindex ('%[^0]%', LEFT(@Var1, len(@Var1)-3)) -1, ''),0)
ELse Case WHEN Right(@Var1, 1) = '0' then
stuff (LEFT(@Var1, len(@Var1)-1), 1, patindex ('%[^0]%', LEFT(@Var1, len(@Var1)-1)) -1, '')
ELSE
stuff (@Var1, 1, patindex ('%[^0]%', @Var1) -1, '')
END
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 18:47:38

Yes. It seems to be simpler than my suggestion.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-23 : 02:28:32

Other one (from Peso's ex)


select txt,ltrim(part_one)+case when part_two>0 then '.'+ltrim(reverse(part_two)) else '' end from
(
select 
	txt,
	cast(left(txt,charindex('.',txt)-1) as int) as part_one,
	cast(reverse(substring(txt,charindex('.',txt)+1,len(txt))) as int)  as part_two 
from @Sample
) as t

But always use proper datatype to store data

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources