convert question please help

hi experts,
i have coordinates stored in a database in form of 452031 5222500 they are float and i want to change them to 4.52031 and 52.22500 so always take the first number then put the dot then the rest and with the second one take the first 2 numbers put the dot and then the rest, i realized that if i did it with the comma instead of the dot and then convert these 2 in string then it will change the comma to the dot automatically, the result can be also a string.
any help will be very appreciated.

thanksHey mrjoka,

Try this. Hope its what you were lookign for.

Cheers,
Justin

-----------------------
-- Declare variables
declare @.number_1 int
declare @.number_1_len int
declare @.number_2 int
declare @.number_2_len int

-- initialise variables
set @.number_1 = 452031
-- get length of number_1
set @.number_1_len = (select len(@.number_1))
set @.number_2 = 5222500
-- get length of number_2
set @.number_2_len = (select len(@.number_2))

-- Format result
select @.number_1 as old_format, CAST(left(@.number_1, 1) as varchar) + '.' + CAST(right(@.number_1, @.number_1_len-1) as varchar) as new_format
select @.number_2 as old_format, CAST(left(@.number_2, 2) as varchar) + '.' + CAST(right(@.number_2, @.number_1_len-2) as varchar) as new_format|||It's Thanksgiving, so I'm feeling especially NZDF today! Enjoy!-- ptp 20071122 see http://www.dbforums.com/showthread.php?t=1624685

DECLARE @.c VARCHAR(50)

SET @.c = '452031 5222500'

SELECT
Stuff(Left(@.c, CharIndex(' ', @.c)), 2, 0, '.')
, LTrim(Stuff(SubString(@.c, PatIndex('% [0-9]%', @.c), 50), 4, 0, '.'))-PatP|||thanks for thw quick reply, but i have a table called location where i have 1652 records i want just column X and Y to be converted.
any idea how to do that?

thanks|||hi experts,

Oh, stop it!

Regards,|||you stop it

never mind i found it myself with some string manipulation.

thanks for the help|||Oh geez, I must remember to insert those humour tags next time, that is, if I can remember where I've left them.

Regards,|||thanks for thw quick reply, but i have a table called location where i have 1652 records i want just column X and Y to be converted.
any idea how to do that?

thanksNo, but if you can post the CREATE TABLE statement, and three or more rows of data, then I could probably "wing it" for you.

The general idea would be to write an UPDATE statement that used your source column name instead of the @.c in my example to set the values for the two new columns using the code that I posted. Once you post the schema and a few rows of data, it should only take a minute to cook up and test.

-PatP|||I'm guessing that he means 452031 5222500 are the two values from the columns X and Y, and he wants a "dot" inserted after the first digit of X and after the second digit of Y. I think.
That Stuff function will work like this:

SELECT STUFF(X, 2, 0, '.') AS X, STUFF(Y, 3, 0, '.') AS Y
FROM location

But if you are going to Update that back into columns of type float, you'll probably need a cast.

Is that what you mean mrjoka?|||452031 5222500 they are float and i want to change them to 4.52031 and 52.22500 update daTable
set x = x / 10000
, y = y / 10000|||set x = x / 10000
, y = y / 10000

That's assuming there are always 6 digits in X and 7 digits in Y.|||mrjoka said:

the result can be also a string
and:
i want just column X and Y to be converted

So its kinda unclear whether he wants to update or not|||That's assuming there are always 6 digits in X and 7 digits in Y.no it isn't :) :)|||no it isn't :) :)Eh?

-PatP|||yes!! another canadian!!

i should have said "no it isn't, eh!!"|||A somewhat belated congrats on 10K, BTW!

I must have missed something though... I think that a-dam has a point, in that Mr. Joka wants to shear two digits off the left side, not N digits from the right side. I think that you also need one more zero on your second divisor.

-PatP|||"shear two digits off the left side" would mean that 452031 and 5222500 would become 45.2031 and 52.22500 and he wanted 4.52031

my interpretation was that the decimal should be moved 5 positions to the left, so from 452031 and 5222500 to 4.52031 and 52.22500

"move n positions to the left" is the same as "divide by nth power of ten"

you are absolutely right, i did not count to 5 correctly

the revised query isupdate daTable
set x = x / 100000
, y = y / 100000

my "no it isn't" comment was in response to "That's assuming there are always [x] digits"

and of course "move n positions to the left" or "divide by nth power of ten" works just as correctly on, say, a three-digit number like 937

:)|||Without the schema, we'll never know just what Mr Joka has now... Without knowing what he's got now, we can choreograph the angels in their dance, but they'll still be confined to the head of the pin.

Mr Joka, set the angels free! In other words, please give us the schema and the sample data I requested many posts ago.

-PatP|||in post #1 he said they are float|||in post #1 he said they are float...and displayed examples of them as one unformatted string!

...and described the output as possibly being a string too!

-PatP|||actually he said "i want to change them" and of course there is no output from an UPDATE

and he said "the result can be also a string" which i took to mean it's optional

:)