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

On Wed, 07 Mar 2007 19:59:01 GMT, Lee Peedin
<lpeedinDONOTSPAME@nc.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

On 7 Mar, 20:19, Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
> On Wed, 07 Mar 2007 19:59:01 GMT, Lee Peedin
>
> <lpeedinDONOTSP...@nc.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

What would you be wanting to set the variable to?

On 8 Mar 2007 02:01:36 -0800, "Captain Paralytic"
<paul_lautman@yahoo.com> wrote:

>On 7 Mar, 20:19, Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
>> On Wed, 07 Mar 2007 19:59:01 GMT, Lee Peedin
>>
>> <lpeedinDONOTSP...@nc.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
>
>What would you be wanting to set the variable to?

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 Peedin wrote:

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

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

On 8 Mar, 12:18, Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
> On 8 Mar 2007 02:01:36 -0800, "Captain Paralytic"
>
>
>
>
>
> <paul_laut...@yahoo.com> wrote:
> >On 7 Mar, 20:19, Lee Peedin <lpeedinDONOTSP...@nc.rr.com> wrote:
> >> On Wed, 07 Mar 2007 19:59:01 GMT, Lee Peedin
>
> >> <lpeedinDONOTSP...@nc.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
>
> >What would you be wanting to set the variable to?
>
> 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 -

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

On 8 Mar 2007 05:11:53 -0800, "Captain Paralytic"
<paul_lautman@yahoo.com> wrote:

>> - Show quoted text -
>
>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

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

THANKS
Lee

On Thu, 08 Mar 2007 14:06:39 GMT, Lee Peedin
<lpeedinDONOTSPAME@nc.rr.com> wrote:

>On 8 Mar 2007 05:11:53 -0800, "Captain Paralytic"
><paul_lautman@yahoo.com> wrote:
>
>>> - Show quoted text -
>>
>>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
>
>Exactly - thanks for this - will give it a try later today and report
>back.
>
>THANKS
>Lee

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