can't pick up the RIGHT most character

Hi,
I have a bit of code which I need to take the last character of a field and
place it at the end of the output. (Sorry if the English does not make much
sense – let me explain further)
The first bit of code is fine:
SELECT U_hvdfinal.*
INTO U_T_STEP03F
FROM U_hvdfinal
WHERE U_hvdfinal.hvd IN ('HVDDA1', 'HVDDA2', 'HVDDA3')
It will then create a table with:
123456789 HVDDA2
214567894 HVDDA1
etc
etc
I then wish to add this to an existing table to appear similar to:
Refhvd
123456789 3F-HVDDA -2
214567894 3F-HVDDA -1
depending on what the last character of the hvd field has in it. So if the
field is HVDDA1 then it should endup looking like: 3F-HVDDA-1. However, using
the sql shown below I am coming up with 3F-HVDDA –
UPDATE U_segment
SET SEGMENT = '3F-' + LEFT(U_T_STEP03F.hvd, 5) + ' - ' + +
RIGHT(U_T_STEP03F.hvd, 1)
FROM U_segment
INNER JOIN U_T_STEP03F
ON U_segment.REF = U_T_STEP03F.REF
WHERE U_segment.SEGMENT IS NULL
What am I doing wrong? I have tried a couple of combinations but it’s still
coming up with the same result?
Thanks in advance
Rob
It may be that the last character is not what you think and might be a
space. Try RIGHT(RTRIM(U_T_STEP03F.hvd), 1)
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:03C5168A-5396-481F-BA2F-12EF07046240@.microsoft.com...
> Hi,
> I have a bit of code which I need to take the last character of a field
> and
> place it at the end of the output. (Sorry if the English does not make
> much
> sense - let me explain further)
> The first bit of code is fine:
> SELECT U_hvdfinal.*
> INTO U_T_STEP03F
> FROM U_hvdfinal
> WHERE U_hvdfinal.hvd IN ('HVDDA1', 'HVDDA2', 'HVDDA3')
>
> It will then create a table with:
> 123456789 HVDDA2
> 214567894 HVDDA1
> etc
> etc
> I then wish to add this to an existing table to appear similar to:
> Ref hvd
> 123456789 3F-HVDDA -2
> 214567894 3F-HVDDA -1
> depending on what the last character of the hvd field has in it. So if the
> field is HVDDA1 then it should endup looking like: 3F-HVDDA-1. However,
> using
> the sql shown below I am coming up with 3F-HVDDA -
>
> UPDATE U_segment
> SET SEGMENT = '3F-' + LEFT(U_T_STEP03F.hvd, 5) + ' - ' + +
> RIGHT(U_T_STEP03F.hvd, 1)
> FROM U_segment
> INNER JOIN U_T_STEP03F
> ON U_segment.REF = U_T_STEP03F.REF
> WHERE U_segment.SEGMENT IS NULL
> What am I doing wrong? I have tried a couple of combinations but it's
> still
> coming up with the same result?
> Thanks in advance
> Rob
>
|||Or perhaps your SEGMENT column is not wide enough to hold the longer value.
It may be truncating the final character.
Thanks,
Don
"Nik Marshall-Blank" wrote:

> It may be that the last character is not what you think and might be a
> space. Try RIGHT(RTRIM(U_T_STEP03F.hvd), 1)
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:03C5168A-5396-481F-BA2F-12EF07046240@.microsoft.com...
>
>