CODEWITHSUNDEEP
sqlsql-server-2012
aggicd
aggicd

Reputation: 737

SQL Server 2012: update column with 2 columns concatenation

I have 2 columns in a table :[CatId], [ItemId]

CatId is 4 digits and ItemId is 1 or 2. What I want to achieve is to replace the ItemId with the concatenation [CatId]+[ItemId] , but if ItemId is 1 digit then add a 0.

For example:

CatId: 1555, ItemId: 12 -> ItemId: 155512

CatId: 1555, ItemId: 2 -> ItemId: 155502

Upvotes: 0

Views: 35

Answers (3)

Anusha Subashini
Anusha Subashini

Reputation: 397

CREATE TABLE [dbo].[Test](
    [CatId] [char](4) NULL,
    [ItemId] [char](2) NULL
) ON [PRIMARY]


Insert Data 

USE [ABC]
GO

INSERT INTO [dbo].[Test]([CatId] ,[ItemId]) VALUES ('1231','1')
INSERT INTO [dbo].[Test]([CatId] ,[ItemId]) VALUES ('1232','2')
INSERT INTO [dbo].[Test]([CatId] ,[ItemId]) VALUES ('1233','10')
INSERT INTO [dbo].[Test]([CatId] ,[ItemId]) VALUES ('1234','23')
INSERT INTO [dbo].[Test]([CatId] ,[ItemId]) VALUES ('1237','6')

Select  [CatId]+     (Case  WHEN len(ItemId)  =1 THEN '0'+[ItemId]  ELSE [ItemId]  END  ) as DATA  from [dbo].[Test]

**Result** 
DATA
123101 
123202 
123310
123423
123706

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

If you want to update your table then try this option:

UPDATE yourTable
SET ItemId = CatId + RIGHT('00' + ItemId, 2);

This assumes that the CatId and ItemId columns are text and not numbers. If they be numeric, then see Gordon's answer.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

I would do this as

select cast(catId as int) * 100 + cast(itemid as int)

If you want this as a string:

select cast(cast(catId as int) * 100 + cast(itemid as int) as varchar(255))

Upvotes: 1

Related Questions