append single character at specific position in string

21 Jan 2006 4:14 PM

sarah.clarke

Hi everyone, i am in seek of help.

I have a variable (called status) of type char(10). This needs to be
extended to type char(11) and i need to append the character 'N' at this
position. For example, if 'status' is any of the following strings of type
char(10)

NYNYYN NY
NNYYY
Y
NNNY YYYNN

These must be converted to the following strings of type char(11) ..<note
the 'N' at the end of the string>:

NYNYYN NYN
NNYYY N
Y N
NNNY YYYNNN

Would anyone know how this can be done?

Any help most appreciated.

Kindest regards,
Sarah.

21 Jan 2006 4:49 PM

Dan Guzman

Based on you narrative, you seems you can simply concatenate. For example:

DECLARE @status char(10)
SELECT @status = 'NYNYYN NY'
SELECT @status + 'N'

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"sarah.clarke" <s.clarke@nospam.com> wrote in message
news:eSgTNXqHGHA.2300@TK2MSFTNGP15.phx.gbl...
> Hi everyone, i am in seek of help.
>
> I have a variable (called status) of type char(10). This needs to be
> extended to type char(11) and i need to append the character 'N' at this
> position. For example, if 'status' is any of the following strings of type
> char(10)
>
> NYNYYN NY
> NNYYY
> Y
> NNNY YYYNN
>
> These must be converted to the following strings of type char(11) ..<note
> the 'N' at the end of the string>:
>
> NYNYYN NYN
> NNYYY N
> Y N
> NNNY YYYNNN
>
> Would anyone know how this can be done?
>
> Any help most appreciated.
>
> Kindest regards,
> Sarah.
>

21 Jan 2006 5:02 PM

Joe from WI

First off, alter the table and change the column to char(11). That's the
easy part.

update MyTable
set status = left(isnull(status,'') + ' ', 10) + 'N'

Note: There are 10 spaces between the quotes.

What this statement does is to convert any null values to an empty string.
Then, it concatenates 10 spaces.
Then, it takes the left most 10 characters.
Finally, it concatenates the 'N' as the eleventh character.

If you want null values to remain null, simply remove the isnull function.
All the concatenation will return null and the row will still have a null
value in the status column.

Hope that helps,
Joe

Show quote

"sarah.clarke" wrote:

> Hi everyone, i am in seek of help.
>
> I have a variable (called status) of type char(10). This needs to be
> extended to type char(11) and i need to append the character 'N' at this
> position. For example, if 'status' is any of the following strings of type
> char(10)
>
> NYNYYN NY
> NNYYY
> Y
> NNNY YYYNN
>
> These must be converted to the following strings of type char(11) ..<note
> the 'N' at the end of the string>:
>
> NYNYYN NYN
> NNYYY N
> Y N
> NNNY YYYNNN
>
> Would anyone know how this can be done?
>
> Any help most appreciated.
>
> Kindest regards,
> Sarah.
>
>
>

21 Jan 2006 5:19 PM

Joe from WI

oops...my previous post was regarding a column

using a variable:
declare @status char(11)
set @status = 'A'
set @status = left(@status, 10) + 'N'

Hope that helps,
Joe