Click here to Skip to main content
14,972,892 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
   
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 [^]
   
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
   
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