I have a column that for now is text data however are really decimal
numeric values.  I want to take everything on the left of the decimal
and stick it in a new column, same row, called whole_number and stick
everything on the right side of the decimal point into the
decimal_number column, same row.  And do this for the whole table.  Any
help would be appreciated.

Thanks.

JR

One approach to the conversion:

declare @t varchar(30)

set @t = '1234.5678'

select @t,
       convert(int, convert(decimal (12,6), @t)),
       convert(decimal (12,6), @t) -
       convert(int, convert(decimal (12,6), @t))

------------------------------ ----------- -------------------
1234.5678                             1234             .567800

I will assume you can figure out how to write the UPDATE.

Roy Harvey
Beacon Falls, CT


Show quoteHide quote

Thanks for the reply Roy.  Only problem I see with this logic, and I
failed to mention this, I need the decimal values to remain at whatever
length they were in the first place.  Not a static 6 digits.  Reason
for this is the decimal value of the field I am trying to separate is
not a traditional decimal value but something the program it comes from
uses for sort order.  So in normal cases ".1" and ".10" are both the
same, however in this case "1" and "10" are different.  Example:

Values:
'1.1'
'1.2'
'1.3'
'1.4'
'1.5'
'1.6'
'1.7'
'1.8'
'1.9'
'1.10'
'1.11'

Output:

Original        Whole_Number        Decimal_Number
1.1                        1                              1
1.2                        1                              2
1.3                        1                              3
1.4                        1                              4
1.5                        1                              5
1.6                        1                              6
1.7                        1                              7
1.8                        1                              8
1.9                        1                              9
1.10                      1                              10
1.11                      1                              11

Unless I'm going about this wrong, I have found that sorting on the
values does not work properly as they are.  If you have 1.1 and 1.2 and
1.10 as text fields it sorts like:

1.1
1.10
1.2

as it sorts left to right.  If I put these in long fields, it sees the
".1" and ".10" as the same values and not as a one and a ten.  By
separating the whole and decimal like the above I am hoping to be able
to do selects sorting based on the whole_number and then the
decimal_number columns to get the right sort order of the records.

Thanks.

JR



Roy Harvey wrote:
Show quoteHide quote

JR (jrik***@yahoo.com) writes:
Seems like you should keep the two parts as varchar columns, but right-
justify them:

   UPDATE tbl
   SET    whole_number = substring(Original, 1,
                                   charindex('.', Original) - 1)),
          decimal_number = substring(Original, charindex('.', Original) + 1,
                                     len(Original)

   UPDATE tbl
   SET    whole_number = replicate(' ', 10 - len(whole_number)),
          decimal_number = replicate(' ', 10 - len(decimal_number))

Thank you Erland,

I came up with something similar as your first update statement (coming
back here to post my findings) however went with integer based columns
for whole_number and decimal_number.  I'm guessing your comment to keep
the two parts as varchar was for the second update query above, however
if not, can you explain your reasoning for leaving them varchar?  I did
a query like the one you posted first into integer based columns and
things are sorting great.

Thanks again for the input.

JR

JR (jrik***@yahoo.com) writes:
Integer should work fine, as long as you first extract the decimal part
as text. That is, the initial approach the whole string was converted
failed for reasons you pointed out.

My suggestion for varchar was due to a momentary lapse. Integer gives
somewhat better performance for sorting, so why not go with that.

Full Text Setup
Trigger logic question - using If with AND and OR
User Define Database
Max Value in Column Other than Return Value Column
use variable in where statement
Performance question
Selecting Fields not to include in SELECT statement
HELP WITH TRIGGER
Archiving data to a seperate database.
appending data to a text column