SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Manipulating initial string for custom output

    Hi there, I need your appreciated help.

    This is the initial string in table MySQL:
    Code:
    XY00138427
    Manipulate the string:
    Code:
    SELECT CONCAT(
               LEFT('XY00138427',4) ,'-1-'
             , MID('XY00138427',6,2),'0'
             , RIGHT('XY00138427',3)
                           ) _NewStringS;
    For this output:
    Code:
    XY00-1-380427
    But I have this problems:

    1. when the first four characters of the initial string is different from `XY00` I need insert `-2-` in the manipulate string;
    2. when the characters number `5` of the initial string is equal to `8` I need insert `-3-` in the manipulate string;



    Example 1 (the first four characters of the initial string is different from `XY00`):
    Code:
    XY50138427
    Output:
    Code:
    XY50-2-380427

    Example 2 (the characters number `5` of the initial string is equal to `8`):
    Code:
    XY00800953
    Output:
    Code:
    XY00-3-800953
    Can you help me?
    Thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    allow me to introduce you to the CASE expression --
    Code:
    SELECT CONCAT(
             LEFT('XY00138427',4) 
           , CASE WHEN MID('XY00138427',5,1) = '8'
                  THEN '-3-'
                  WHEN LEFT('XY00138427',4) = 'XY00'
                  THEN '-1-'
                  ELSE '-2-' END
             , MID('XY00138427',6,2),'0'
             , RIGHT('XY00138427',3)
                           ) _NewStringS;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Nice to meet you, thanks so much!

  4. #4
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry, but I have new problem... this code working:
    Code:
    SELECT CONCAT(
             LEFT('XY00138427',4) 
           , CASE WHEN MID('XY00138427',5,1) = '8'
                  THEN '-3-'
                  WHEN LEFT('XY00138427',4) = 'XY00'
                  THEN '-1-'
                  ELSE '-2-' END
             , MID('XY00138427',6,2),'0'
             , RIGHT('XY00138427',3)
                           ) _NewStringS;
    When execute in the table MySQL, I have this error:
    [Err] 1271 - Illegal mix of collations for operation 'concat'

    Code:
    SELECT CONCAT(
               LEFT(`myInitialString`,4)
             , CASE WHEN MID(`myInitialString`,5,1) = '8' 
                    THEN '-3-' 
                    WHEN LEFT(`myInitialString`,4) = 'XY00' 
                    THEN '-1-'
                    ELSE '-2-' END
             , MID(`myInitialString`,6,2), '0'
             , RIGHT(`myInitialString`,3)
                           ) _NewStringS FROM tbl_1;


Bookmarks

Posting Permissions

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