Click here to Skip to main content
15,436,928 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I work on SQL server 2012 I face issue I need to convert characters and numbers to underscore "_"
and after that concatenate it based on length of number or charachters
so if one character or one number will be _
so if two character or two number will be __
so if three character or three number will be ___
etc.
so How to make select query do above on test sample below :

SQL
create table portionstable
 (
 PortionKey nvarchar(20)
 )
 insert into portionstable(PortionKey)
 values
 ('123'),
 ('abc'),
 ('a'),
 ('ab'),
 ('12')


select * from portionstable
SQL
___
 ___
 _
 __
 __


What I have tried:

SQL
select concat(replace(PortionKey,PortionKey,'_' )) as portionkeyfrom portionstable
Posted
Updated 8-Jun-21 0:58am

Use REPLICATE:
REPLICATE (Transact-SQL) - SQL Server | Microsoft Docs[^]
SQL
SELECT
    REPLICATE('_', LEN(PortionKey)) As PortionKey
FROM
    portionstable
 
Share this answer
 
Comments
CHill60 8-Jun-21 6:58am    
5'd - couldn't remember the name of the function d'oh.
Maciej Los 8-Jun-21 8:44am    
5ed!
Off the top of my head I would probably do something like
SQL
select replace(space(len(PortionKey)),' ','_')

In hindsight, I ought to explain this...
I generated a load of space characters (space[^]) based on the length of the column (len[^]).
That meant that I knew exactly what I wanted to replace [^]
 
Share this answer
 
v2
Comments
Richard Deeming 8-Jun-21 6:55am    
REPLICATE is easier. :)
CHill60 8-Jun-21 7:04am    
Hence my comment on your solution :-)
Maciej Los 8-Jun-21 8:45am    
Good one!
CHill60 8-Jun-21 8:47am    
Ah, but Richard's is better :)
Try this:
SQL
SELECT REPLICATE('_', LEN(PortionKey)) FROM PortionsTable
 
Share this answer
 
Comments
Richard Deeming 8-Jun-21 7:02am    
Snap! 😁
OriginalGriff 8-Jun-21 8:19am    
There were no answers when I started! :laugh:

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900