How can i add prefix to any field data?

_{Sunday, August 29, 2010 12:25 PM}

I have a table 'CLIENT' where i want to prefix 99 to all the existing customer IDs in the integer field 'CUID'. How can i do this?

For eg.

CUID

12

123

1234

will be:

CUID

9912

99123

991234

LuxCoder

_{Sunday, August 29, 2010 12:50 PM ✅Answered | 1 vote}

Try

UPDATE Client
SET CUID = '99' + CAST (CUID AS VARCHAR(10))

based on sample data  you provided

DECLARE @Client TABLE
(
 CUID INT
)

INSERT INTO @client
SELECT 12
UNION ALL
SELECT 123
UNION ALL
SELECT 1234

update @client
set cuid = '99' + CAST(CUID AS Varchar(10))

select * from @client

_{Wednesday, September 1, 2010 7:36 AM}

Thank you Chirag!!

What will be the reverse of this query??

Like if i want to remove '99'?

Thanks.

LuxCoder

_{Wednesday, September 1, 2010 7:52 AM}

Well, if you want to store the data natively as the 1234 and only want to prefix it for certain circumstances, you might want to take a look at a computed column, this will make it possible to generate the values during the Select. A sample you be:

USE TEMPDB

CREATE TABLE SomeTableCompColumn
(
 TheIntValue INT,
 TheComputedValue AS '99' + CAST(TheIntValue AS VARCHAR(50))
)

GO

INSERT INTO SomeTableCompColumn(TheIntValue)
VALUES (1),(2),(1234)

SELECT * FROM SomeTableCompColumn

TheIntValue TheComputedValue

1           991
2           992
1234        991234

(3 row(s) affected)

 -Jens

Jens K. Suessmeyer http://blogs.msdn.com/Jenss

_{Wednesday, September 1, 2010 1:04 PM}

Thank you Chirag!!

 

What will be the reverse of this query??

Like if i want to remove '99'?

 

Thanks.

LuxCoder

another alternative is to use  Substring function

i.e.

DECLARE @Client TABLE
(
 CUID INT
)

INSERT INTO @client
SELECT 12
UNION ALL
SELECT 123
UNION ALL
SELECT 1234

update @client
set cuid = '99' + CAST(CUID AS Varchar(10))

select * from @client

update @client
set cuid = SUBSTRING(CAST(CUID AS VARCHAR(10)), 3, LEN(CAST (CUID AS VARCHAR(10)) ))

select * from @client

_{Wednesday, September 1, 2010 6:17 PM}

Or

update @client
set cuid = RIGHT(ciud, LEN(cuid)-2)

-Jens

Jens K. Suessmeyer http://blogs.msdn.com/Jenss

_{Friday, January 13, 2012 2:02 AM}

Hi Buddy, Thank you very much!!!!!! Chirag!!This code is very use full for me. Many thanks, Mohammad Alikish

_{Friday, January 13, 2012 2:02 AM}

Hi Buddy, Thank you very much!!!!!! This code is very use full for me. Many thanks, Mohammad Alikish