CODEWITHSUNDEEP
sql-server-2008
Dan
Dan

Reputation: 773

Integers and decimals to varchar sql-server

I have a set of numbers 1-55. Including three decimals between each (x.1, x.2 and x.3) where the value of the number is not important, yet the string of characters is. (i.e. 1, 1.1, 1.2, 1.3, 2, 2.1, 2.2, 2.3, 3,...55). I am having trouble with keeping the format of the whole numbers to just the whole number character (i.e. 1,2,3,4... instead of 1.0, 2.0, 3.0, 4.0). These are two completely different things.

I’m pretty sure I need to be able to store this set as a char/varchar/text but it’s difficult when I can’t figure out how to get them all stored as character strings.

Any help would be appreciated!
Thanks!

Edit: May be pertinent and I apologize because I I figured my question at face value was complete. It was not. There will be at least 30 columns that contain frequencies associated with each of these numbers. The numbers (1-55 with the decimals between as listed above) will be used in a comparison process to return the frequency (ie get me the frequency that matches 22.3, or 30). In some instances the values being compared to my table will have letters and symbols, thus I figured that I would need these stored as characters rather than numbers. Explicitly, 1, 1.1, 1.2, 1.3, 2, 2.1, 2.2 etc up to 55 as a varchar(4) rather than a decimal(3,1) because a decimal (3,1) can’t be compared to a varchar(4)...can it?

The problem I am having is that when I add values 1, 2, 3 as varchar (4) data types to the table and query it, sql returns 1, 2, 3 but when I also add 1.1, 1.2 and 1.3 then query it, my numbers 1, 2, 3 return as 1.0, 2.0 and 3.0. I guess maybe the better question would be, is a 1 as a varchar data type the same as a 1.0 varchar datatype? I was thinking no. How can I force the character 1 to remain 1 and not change to 1.0 when I add other data to the table that has .1, .2 or .3 after it?

Upvotes: 0

Views: 282

Answers (1)

hpatel
hpatel

Reputation: 199

your question is confusing, however, please check code below with some comments if it helps you achieve what you want.

-- Populate numbers as varchar(4)
DECLARE @Numbers TABLE (Number varchar(4)); 
DECLARE @Number INT

SET @Number = 1

WHILE @Number <= 55
BEGIN
    INSERT INTO @Numbers VALUES(@Number);
    INSERT INTO @Numbers VALUES(@Number + 0.1);
    INSERT INTO @Numbers VALUES(@Number + 0.2);
    INSERT INTO @Numbers VALUES(@Number + 0.3);
    SELECT @Number = @Number + 1
END

SELECT Number FROM @Numbers

--You can compare decimal value with table
DECLARE @DecimalNumber AS decimal(3,1)

SET @DecimalNumber=22.3
SELECT Number FROM @Numbers WHERE Number=@DecimalNumber

SET @DecimalNumber=30
SELECT Number FROM @Numbers WHERE Number=@DecimalNumber

--You can also compare varchar value with table
DECLARE @VarcharNumber varchar(4)

SET @VarcharNumber='40'
SELECT Number FROM @Numbers WHERE Number=@VarcharNumber

Let me know if you can clarify further and/or provide sample code/data.

Upvotes: 1

Related Questions