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

Question

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
June 8, 2021 at 10:25 am

#3893330
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
 ___
 ___
 _
 __
 __
```

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2177 More actions · Answer 1

The least friction way imo seems to be to split each character using fnTally and convert to ASCII values. Then re-aggregate the string. Something like this (with extra non-[a-z][0-9]) characters added)

drop table if exists #portionstable;
go
create table #portionstable(
  PortionKey nvarchar(20));

 insert into #portionstable(PortionKey)
 values
 ('1%2&3'),
 ('a!bc'),
 ('a'),
 ('ab'),
 ('1-2');

 select PortionKey, string_agg(v.chars, '') within group (order by fn.n) replaced_chars
  from #portionstable p
      cross apply dbo.fnTally(1, len(p.PortionKey)) fn
      cross apply (values (case when ascii(substring(lower(PortionKey), fn.n, 1)) 
                                     between 91 and 122 then '_' /*a to z*/
                                when ascii(substring(lower(PortionKey), fn.n, 1)) 
                                     between 48 and 57 then '_' /*0 to 9*/
                                else substring(PortionKey, fn.n, 1) end)) v(chars)
 group by PortionKey;

PortionKeyreplaced_chars
1%2&3_%_&_
1-2_-_
a_
a!bc_!__
ab__

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

This seems to be really destructive. Are you just trying to do "Data Masking"?

https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15

If you really do want what you've stated, then it's real simple (using the test table you posted)...

 SELECT  OriginalPortionKey = PortionKey
        ,Underscores        = REPLICATE('_',LEN(PortionKey))
   FROM dbo.portionstable
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)