Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2008
    Posts
    4

    Unanswered: split strings in sql server 2000

    Hello,
    I am using sql server 2000.
    I have a query which is:
    Code:
    SELECT DESCR 
    FROM  MERCHANDISE
    WHERE DESCR LIKE '%'+UPPER('TV')+'%'
    Then, the result becomes
    Code:
    19/20" TV
    25/27" TV
    FRONT PROJECTION TV
    ANALOG PROJECTION TV
    BLACK & WHITE TV
    TV DISPLAY PRODUCT
    13" TV
    31"+ ANALOG TUBE TV
    COMBO TV
    UP TO 20 LCD TV
    LESS 29 DIG TUBE TV
    21-29 LCD TV
    37 + LDC TV
    ...
    Please notice each row contains the keyword "TV".
    What I expected result should look like below.
    Code:
    19/20"
    25/27"
    FRONT
    PROJECTION
    ANALOG
    PROJECTION
    BLACK
    &
    WHITE
    DISPLAY
    PRODUCT
    13"
    31"+
    ANALOG
    TUBE
    COMBO
    UP
    TO
    20
    LCD
    LESS
    29
    DIG
    TUBE
    21-29
    LCD
    37
    +
    LDC
    You see there is a difference comparing the previous result.
    1. Every row is splitted.
    2. "TV" is removed
    3. Each row only has one word.

    And please only consider query in sql server 2000. If you define a variable, the maximum length is
    only 8000, probably you will have trouble.

    I have been working on this for several days but only get depressness.
    Hope anybody can help me.
    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd split the data on the client side instead of in the SQL Server. It is both easier to write and faster to run.

    -PatP

  3. #3
    Join Date
    Sep 2008
    Posts
    4
    Would you please post your code?

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    @ret = split($descr)
    -PatP

  5. #5
    Join Date
    Sep 2008
    Posts
    4
    But I have to do it in server side.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why ?
    George
    Home | Blog

  7. #7
    Join Date
    Sep 2008
    Posts
    4
    Ignore the server side.
    Even in the client side.
    Code:
    @ret = split($descr)
    is not working.
    Can you please post your whole code?

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I think you need to use an array with the split function if memory serves.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This has to be the oddest requirement I have ever seen.

    But @ret is an array. No restriction has been put on the client side, as yet, so we still have the luxury of picking the language.

    Are we limited to ZDF solutions here?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My code sample was written in Perl.

    -PatP

  11. #11
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Try this

    SELECT REPLACE(DESCR ,UPPER('TV'),'')
    FROM MERCHANDISE
    WHERE DESCR LIKE '%'+UPPER('TV')+'%'

  12. #12
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    The above query replaces all the upper case TV to '', if you are sure that TV only comes at the end of the the string then remove the last wild card character.(%)

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This may be more robust:
    SELECT LTRIM(RTRIM(REPLACE(' ' + @DESCR + ' ' ,UPPER(' TV '),' ')))
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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