How to convert characters or number to underscore and after that concatenate it

Transact-SQL
, ,
1

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

 create table portionstable
 (
 PortionKey nvarchar(20)
 )
 insert into portionstable(PortionKey)
 values
 ('123'),
 ('abc'),
 ('a'),
 ('ab'),
 ('12')
    
 select * from portionstable
 ___
 ___
 _
 __
 __

what i try but not working

select concat(replace(PortionKey,PortionKey,'_' )) as portionkeyfrom portionstable
so how to do it

2

hi

one idea is to do multiple replaces for all numbers and alphabets ( you can put this inside a function )
select replace 1 replace 2 replace 3

or

please see if regular expressions can be used

or

you can use C# or some other language other than T-SQL to do it

Hope this helps :slight_smile:

3

No need for REPLACE at all:

SELECT REPLICATE(N'_',LEN(PortionKey)) FROM PortionsTable
1 Like
4

Nice Idea Robert Volk :+1: :+1:

if all the characters are numbers or letters

5

That's the same answer I provided over at SQLServerCentral.

6

Brilliant minds think alike :wink:

7

Heh... thank you but we may not be so brilliant if the requirement is to not replace spaces (and other non-alphanumeric characters) in a multi-word string. :smiley: That would require a slightly different set of hammers (DelimitedSplit8K and STRING_AGG, if the later is available). :smiley:

8

TRANSLATE() might be an option too, and would be the best solution.

9

Although I've not had to use TRANSLATE in the past, I can definitely see it fitting in there quite nicely. I wonder what the performance of the function would actually be?

Heh... but first, I wish @ahmedbarbary would tell us why this needs to be done to begin with! :smiley: