CODEWITHSUNDEEP
sqlsql-server
Aimar
Aimar

Reputation: 7

Concatenate characters according to a some number

I want to write a SQL script that can concatenate a character like a comma according to a some number

Example

UPDATE Table1 
SET column1 = column1 + '.'
WHERE LEN(column1) = 2

UPDATE Table1 
SET column1 = column1 + '..'
WHERE LEN(column1) = 3

UPDATE Table1 
SET column1 = column1 + '...'
WHERE LEN(column1) = 4

I want to render this script automatic

Thanks.

Upvotes: 0

Views: 37

Answers (2)

deterministicFail
deterministicFail

Reputation: 1285

this will generate you the updates

SELECT 
   'UPDATE Table1 SET column1 = column1 + ''' + REPLICATE('.', LEN(column1) -1) + ''' WHERE LEN(column1) = ' + CONVERT(VARCHAR, LEN(column1))
FROM 
   Table1
WHERE
   LEN(column1) > 1
GROUP BY LEN(column1)

Upvotes: 0

ASh
ASh

Reputation: 35730

this query adds 3 dots '.' to all values in column1, which have length = 3

declare @chr varchar(1) = '.'
declare @len int = 3

UPDATE Table1 
SET column1 = column1 + replicate(@chr, LEN(column1))
WHERE LEN(column1) = @len -- where clause is optional

without where, the query will update ALL rows and add LEN(column1) dots

Upvotes: 2

Related Questions