Converting char to numeric - SQL Server Forums

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)

Converting char to numeric

Author

Topic

Nesu
Starting Member

15 Posts

Posted - 2007-06-04 : 07:41:24

Hi all.

Have a problem with converting some a field.

What I want is to convert the height field from a char to a numeric. For example, I want to select a range of values from 1 to 20, but at the moment it will only do 1 to 9 unless I specify a like condition wich is very tedious and impractical in future queries where the values will range from 70 to 210.

I've tried using a convert to integer, but doing so loses the decimal point. The value in the height field will always be 8 characters but the position of the decimal point will change.

I get the feeling that if there is no covert function to this I may have to use cast, but I'm not sure how to progress with that.

select d.userid, v.height convert (int (8), (height)) into testing from THN_RW.dbo.driver as d
inner join dbo.values as v on d.userid = v.userid
where (d.userid = v.userid)

This is the code I've tried to use to convert but as you can probably see, the code won't convert and keep the decimal in place. I tried using decimal, but that just returned errors so I'm at a loss as to how best to proceed.

Any help would be most appreciated.

Thanks

Nesu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-04 : 07:51:29

Do you have some sample data and expected output?

Peter Larsson
Helsingborg, Sweden

Nesu
Starting Member

15 Posts

Posted - 2007-06-04 : 08:10:11

userid height output i would like
a1 3.400000 3.400000 as a numeric field
a2 12.60000 12.60000 as a numeric field
a3 1.660000 1.660000 as a numeric field
etc

At the moment if i use a between statement, which is what i want to do, it won't cover all the values. I have to specify every value before the decimal in its own like statement. eg like '1%' like '2%' like '3%' etc up to 20. If I can convert the height data to a decimal or some numeric field keeping the decimal point, I can use a between statement for the large ranges where the units change using the newly created table.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2007-06-04 : 08:20:01

quote:
I've tried using a convert to integer, but doing so loses the decimal point.

Correct - the integer datatype is whole numbers
SELECT CAST(123.456789 AS int) --Result is 1

quote:
I get the feeling that if there is no covert function to this I may have to use cast

CAST and CONVERT are similar in functionality, just a different syntax. CAST is SQL-92 compliant
e.g
SELECT CAST('123.456789' AS float) --Result is 123.456789
SELECT CONVERT(float,'123.456789') --Result is 123.456789

Look up CAST and CONVERT in BOL

Beauty is in the eyes of the beerholder

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2007-06-04 : 08:30:51

[code]--Set up sample data
DECLARE @t TABLE(height char(8))
INSERT @t(height)
SELECT '3.400000' UNION ALL
SELECT '12.60000' UNION ALL
SELECT '1.660000'

SELECT *
FROM @t
WHERE CAST(height AS numeric(10,8)) BETWEEN 1.66 AND 3.39
[/code]

Beauty is in the eyes of the beerholder

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-04 : 08:34:14

As numbers are stored in char datatype, you need to check if all values are numeric

Madhivanan

Failing to plan is Planning to fail

Nesu
Starting Member

15 Posts

Posted - 2007-06-04 : 10:50:44

Thanks all.

I used the below to solve the problem and it works fine now.

Thanks again.

quote:
Originally posted by AndyB13
[SELECT CONVERT(float,'123.456789') --Result is 123.456789

Kristen
Test

22859 Posts

Posted - 2007-06-05 : 04:31:44

You should consider storing the value in a Numeric datatype column, rather than char. That will prevent any value being saved which, for any reason, is not actually numeric.

You could check for illegal positive floating point values using:

SELECT * FROM MyTable WHERE MyColumn LIKE '%[^0-9.]%' OR MyColumn LIKE '%.%.%'

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources