Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Oct 2006
    Posts
    55

    Unanswered: 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.

    thanks
    Peace out
    Mr joka

  2. #2
    Join Date
    Nov 2007
    Location
    South Africa
    Posts
    1
    Hey 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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It's Thanksgiving, so I'm feeling especially NZDF today! Enjoy!
    Code:
    --  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

  4. #4
    Join Date
    Oct 2006
    Posts
    55
    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
    Peace out
    Mr joka

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by mrjoka
    hi experts,
    Oh, stop it!

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Oct 2006
    Posts
    55
    you stop it

    never mind i found it myself with some string manipulation.

    thanks for the help
    Peace out
    Mr joka

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Oh geez, I must remember to insert those humour tags next time, that is, if I can remember where I've left them.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by mrjoka
    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
    No, 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

  9. #9
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56
    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?
    Don't Bogart That Squishee!

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mrjoka
    452031 5222500 they are float and i want to change them to 4.52031 and 52.22500
    Code:
    update daTable
       set x = x / 10000 
         , y = y / 10000
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56
    set x = x / 10000
    , y = y / 10000

    That's assuming there are always 6 digits in X and 7 digits in Y.
    Don't Bogart That Squishee!

  12. #12
    Join Date
    Dec 2006
    Location
    Momence, IL USA
    Posts
    56
    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
    Don't Bogart That Squishee!

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by a-dam
    That's assuming there are always 6 digits in X and 7 digits in Y.
    no it isn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    no it isn't
    Eh?

    -PatP

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes!! another canadian!!

    i should have said "no it isn't, eh!!"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •