Adding number to text rows sql server

Question

I have a columns named id and item and there are stored values like:

id item
1  value
2  value
3  value

etc. There are 192 rows. These values are in the system in different places and I need to find concrete value in database to change it to the name I need.

Is there some posibility to add number to rows, for example value_01, value_02 etc.

I know how to do it in C language, but have no idea how to do it in sql server.

Edited: @lad2025

In system I have columns, that names are stored in database. Names are same, for example:

In app Apple I have table name Apple

In app Storage I also have table name Apple

I need to change app Storage columns name Apple to different, but I dont know, which of databasa Apple values it is, so I want to add identifiers to string, to find the right one. So I need to update database values, to see them in system.

Lukasz Szozda · Accepted Answer · 2015-09-14 07:32:22Z

2

SQLFiddleDemo

DECLARE @pad INT = 3;

SELECT
[id],
[item] = [item] + '_' + RIGHT(REPLICATE('0', @pad) + CAST([id] AS NVARCHAR(10)), @pad)
FROM your_table;

This will produce result like:

value_001
value_010
value_192

EDIT:

After reading your comments it is not clear what you want to achieve, but check:

SqlFiddleDemo2

DECLARE @pad INT = 3;

;WITH cte AS
(
  SELECT *,
    [rn] = ROW_NUMBER() OVER (PARTITION BY item ORDER BY item)
  FROM your_table

)
SELECT
[id],
[item] = [item] + '_' + RIGHT(REPLICATE('0', @pad) + CAST([rn] AS NVARCHAR(10)), @pad)
FROM cte
WHERE item = 'value';  /* You can comment it if needed */

edited Sep 14, 2015 at 7:32

answered Sep 14, 2015 at 7:00

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Felix Pamittan Over a year ago

Why use REPLICATE instead of '000'?

elly Over a year ago

And if there is some other value name too, than I just need to add where value name = 'value'??

Lukasz Szozda Over a year ago

Because when I want more 0 I just change '3' to '5' like DECLARE @pad INT = 5; ... [item] = [item] + '_' + RIGHT(REPLICATE('0', @pad) + CAST([id] AS NVARCHAR(10)), @pad)

Felix Pamittan Over a year ago

You should've included that in your answer.

Lukasz Szozda Over a year ago

@elly Yes if there is for example product you can filter it out using WHERE item = 'value'

|

Collectives™ on Stack Overflow

Adding number to text rows sql server

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related