Professional Web Applications Themes

Need help with CONCAT and character substitution - MySQL

Table name: cvmorans All columns are varchar farmno has data like 002004, 006007,003456 honum has data like 001,004,006 lotno has data like 10,25,78 Task 1 - substitute the 3 character of farmno with a, "F" if 3, "N" if 6, otherwise, no substitution Task 2 - CONCAT right(farmno,4),right(honum,1), lotno End result for the above samples: F004110 N007425 3456678 And I have made an attempt (successful, but not completely what I need) select if(substr(farmno,3,1) = 2, concat('F',right(farmno,3),right(honum,1),lotno), concat('N',right(farmno,3),right(honum,1),lotno)) as lotnumber from cvmorans This will only substitute for a 2 or 6 in the 3rd position. Can I use an IF inside ...

  1. #1

    Default Need help with CONCAT and character substitution


    Table name: cvmorans

    All columns are varchar

    farmno has data like 002004, 006007,003456

    honum has data like 001,004,006

    lotno has data like 10,25,78

    Task 1 - substitute the 3 character of farmno with a, "F" if 3, "N" if
    6, otherwise, no substitution

    Task 2 - CONCAT right(farmno,4),right(honum,1), lotno

    End result for the above samples:
    F004110
    N007425
    3456678

    And I have made an attempt (successful, but not completely what I
    need)

    select if(substr(farmno,3,1) = 2,
    concat('F',right(farmno,3),right(honum,1),lotno),
    concat('N',right(farmno,3),right(honum,1),lotno))
    as lotnumber from cvmorans

    This will only substitute for a 2 or 6 in the 3rd position.

    Can I use an IF inside of an IF?

    Any help or advice is greatly appreciated.

    Lee

    Lee Guest

  2. #2

    Default Re: Need help with CONCAT and character substitution

    On Wed, 07 Mar 2007 19:59:01 GMT, Lee Peedin
    <rr.com> wrote:

    May have answered my own question :-)

    select
    case substr(farmno,3,1)
    when 2 then concat('F',right(farmno,3),right(honum,1),lotno)
    when 6 then concat('N',right(farmno,3),right(honum,1),lotno)
    else concat(right(farmno,4),right(honum,1),lotno)
    end as lotnumber,
    wend,mcount,refno from cvmorans

    Now I'm wondering how I might set a "variable" and use it in "one"
    CONCAT string??

    TIA
    Lee

    Lee Guest

  3. #3

    Default Re: Need help with CONCAT and character substitution

    On 7 Mar, 20:19, Lee Peedin <rr.com> wrote: 

    What would you be wanting to set the variable to?

    Captain Guest

  4. #4

    Default Re: Need help with CONCAT and character substitution

    On 8 Mar 2007 02:01:36 -0800, "Captain Paralytic"
    <com> wrote:
     
    >
    >What would you be wanting to set the variable to?[/ref]

    Just wondering if there was a way to set a variable to the to the 3rd
    character if no substitution is needed or F/N if substitution is
    needed - then one concat that would concatinate the variable with the
    other pieces.

    The above works fine - just wondering if there was a better/more
    optimized way of doing this.

    Thanks
    Lee
    Lee Guest

  5. #5

    Default Re: Need help with CONCAT and character substitution

    Lee Peedin wrote:
     

    You could look at REGEXP, not sure if you can do all that in one go, but you
    could try. Just remember that REGEXP may not work if you move your SQL query
    to another database.


    --

    //Aho
    J.O. Guest

  6. #6

    Default Re: Need help with CONCAT and character substitution

    On 8 Mar, 12:18, Lee Peedin <rr.com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Just wondering if there was a way to set a variable to the to the 3rd
    > character if no substitution is needed or F/N if substitution is
    > needed - then one concat that would concatinate the variable with the
    > other pieces.
    >
    > The above works fine - just wondering if there was a better/more
    > optimized way of doing this.
    >
    > Thanks
    > Lee- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Sort of like:

    select
    case substr(farmno,3,1)
    when 2 then v := 'F'
    when 6 then v := 'N'
    else substr(farmno,3,1)
    end,
    concat(v,right(farmno,3),right(honum,1),lotno) lotnumber,
    wend,mcount,refno from cvmorans

    Alternatively:

    select
    v := substr(farmno,3,1),
    case v
    when 2 then v := 'F'
    when 6 then v := 'N'
    end,
    concat(v,right(farmno,3),right(honum,1),lotno) lotnumber,
    wend,mcount,refno from cvmorans

    Captain Guest

  7. #7

    Default Re: Need help with CONCAT and character substitution

    On 8 Mar 2007 05:11:53 -0800, "Captain Paralytic"
    <com> wrote:
     
    >
    >Sort of like:
    >
    >select
    >case substr(farmno,3,1)
    >when 2 then v := 'F'
    >when 6 then v := 'N'
    >else substr(farmno,3,1)
    >end,
    >concat(v,right(farmno,3),right(honum,1),lotno) lotnumber,
    >wend,mcount,refno from cvmorans
    >
    >Alternatively:
    >
    >select
    >v := substr(farmno,3,1),
    >case v
    >when 2 then v := 'F'
    >when 6 then v := 'N'
    >end,
    >concat(v,right(farmno,3),right(honum,1),lotno) lotnumber,
    >wend,mcount,refno from cvmorans[/ref]

    Exactly - thanks for this - will give it a try later today and report
    back.

    THANKS
    Lee
    Lee Guest

  8. #8

    Default Re: Need help with CONCAT and character substitution

    On Thu, 08 Mar 2007 14:06:39 GMT, Lee Peedin
    <rr.com> wrote:
     
    >>
    >>Sort of like:
    >>
    >>select
    >>case substr(farmno,3,1)
    >>when 2 then v := 'F'
    >>when 6 then v := 'N'
    >>else substr(farmno,3,1)
    >>end,
    >>concat(v,right(farmno,3),right(honum,1),lotno ) lotnumber,
    >>wend,mcount,refno from cvmorans
    >>
    >>Alternatively:
    >>
    >>select
    >>v := substr(farmno,3,1),
    >>case v
    >>when 2 then v := 'F'
    >>when 6 then v := 'N'
    >>end,
    >>concat(v,right(farmno,3),right(honum,1),lotno ) lotnumber,
    >>wend,mcount,refno from cvmorans[/ref]
    >
    >Exactly - thanks for this - will give it a try later today and report
    >back.
    >
    >THANKS
    >Lee[/ref]

    These worked with one exception:
    Had to change the "else" statement in the first example
    from:
    else substr(farmno,3,1)
    to:
    else v := substr(farmno,3,1)

    Thanks again,
    Lee

    Lee Guest

Similar Threads

  1. concat problem
    By GarryJones in forum MySQL
    Replies: 5
    Last Post: December 27th, 06:55 PM
  2. concat with nulls
    By rmorgan in forum Coldfusion Database Access
    Replies: 3
    Last Post: November 13th, 03:12 PM
  3. Concat two fields in SQL
    By thomascraig in forum Coldfusion Database Access
    Replies: 7
    Last Post: June 9th, 01:54 PM
  4. php string concat
    By anybody in forum PHP Development
    Replies: 2
    Last Post: October 14th, 03:04 PM
  5. Concat as Parameter not possible ???
    By Harry in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 29th, 10:00 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139