append single character at specific position in string

Hi everyone, i am in s 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.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
"sarah.clarke" <s.clarke@.nospam.com> wrote in message
news:eSgTNXqHGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Hi everyone, i am in s 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.
>|||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
"sarah.clarke" wrote:

> Hi everyone, i am in s 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.
>
>|||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