How to convert characters or number to underscore and after that concatenate it based on number of character or numbers ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 8-Jun-21 0:40am

ahmed_sa

Updated 8-Jun-21 0:58am

Add a Solution

3 solutions

Solution 2

Use REPLICATE:
REPLICATE (Transact-SQL) - SQL Server | Microsoft Docs[^]

SQL

SELECT
    REPLICATE('_', LEN(PortionKey)) As PortionKey
FROM
    portionstable

Posted 8-Jun-21 0:55am

Richard Deeming

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!

Solution 1

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 [^]

Posted 8-Jun-21 0:51am

CHill60

Updated 8-Jun-21 0:54am

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 :)

Add a Solution

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

OriginalGriff · Accepted Answer · 2021-06-08T00:58:00

Solution 3

Try this:

SQL

SELECT REPLICATE('_', LEN(PortionKey)) FROM PortionsTable

Posted 8-Jun-21 0:58am

OriginalGriff

Comments

Richard Deeming 8-Jun-21 7:02am

Snap! 😁

OriginalGriff 8-Jun-21 8:19am

There were no answers when I started! :laugh:

How to convert characters or number to underscore and after that concatenate it based on number of character or numbers ?

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

How to convert characters or number to underscore and after that concatenate it based on number of character or numbers ?

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us