Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2006
    Posts
    58

    Unanswered: How to transform "full width" String into "half width" String?

    Hi, everyone

    There is a table t1 with two fields, such as,
    ID NAME
    1   Tokyo
    2 Xian
    3 America

    For there are full-width and half-width strings in the values of the two fields, I can not select and get the right records. So I want to transform the two fields ID and NAME, I fail to find the function in the SQL Server 2005. Please give me some advice.

    Thank you very much!

  2. #2
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    try trimming all the blank spaces in the fields before running any select/ use the fields with Trim() running on them.
    In GOD we believe. Everything else we Test!

  3. #3
    Join Date
    Aug 2006
    Posts
    58
    thank you, wash.

    The function trim only trims the blank spaces in the field.However, field values with full width or half width do not mean there are blank spces in the field.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I have no idea what you are talking about

    what are full/hald width strings?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think I know what he wants.

    If all the data consists of single words, you can eliminate all the spaces using the REPLACE function:
    Code:
    select replace('t o k y o ', ' ', '')
    ...but if some of the records contain multiple words, you are out of luck.
    How did your data end up looking like this in the first place?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How about this:
    Code:
    select substring (name, 1, length(name)/2)
    Truncates half the string, every time. May not be what you want, but....

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do these guys even get emails when we reply?

    Half?

    Ever hear of RI?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Aug 2006
    Posts
    58
    sorry, Brett Kaiser , I did not get your email.

  9. #9
    Join Date
    Aug 2006
    Posts
    58
    thanks all.

    I still failed in the problems with full width and half width. If possible, please create a table, and insert some records with full-width and half-width string, you will find it is difficult to cope with the problem.

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    here's a variation of what blindman suggested that will work for your funny-spaced words as long as they are separated by two spaces between words:
    Code:
    select replace(replace(replace('t o k y o  n i g h t s', '  ', '__ACK_ACK__'),' ',''),'__ACK_ACK__',' ')
    You can replace the __ACK_ACK__ with any string you like as long as it's guaranteed never to appear in the strings you are operating on. I figure __ACK_ACK__ is pretty unlikely to appear since it's Martian.

  11. #11
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    no problem will have a solution untill you define your problem statement clearly.
    In GOD we believe. Everything else we Test!

Posting Permissions

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