Scenario: column contains numbers as ('1234', '123', '23456') - I need to replace the first number with 'A' and replace the remaining numbers with *.
Output should be:
A***
A**
A****
Can anyone help me? How to achieve this?
Thanks
3 Answers
REPLICATE is best option for you.
CREATE TABLE #TAB(VAL VARCHAR(100))
INSERT INTO #TAB
VALUES ('1234'), ('123'), ('23456')
SELECT 'A'+REPLICATE('*', LEN(VAL)-1) FROM #TAB
5 Comments
Esperento57
Same answer of me :)
Esperento57
be carefull to null value or value with len <= 1
Shakeer Mirza
Thanks for pointing. And I started writing answer on own. @Esperento57
Esperento57
your example dont bug with null value or len <=1, it give null as result. but is that what he wants as a result...
Ahyan
Thanks all I used the replicate and it works as expected. Thanks everyone
You can try this
Drop Table #MyTempTable
Create Table #MyTempTable (
NAME NVARCHAR(100)
);
Insert Into #MyTempTable VALUES ('3243')
Insert Into #MyTempTable VALUES ('123')
Insert Into #MyTempTable VALUES ('432423')
Insert Into #MyTempTable VALUES ('423')
Insert Into #MyTempTable VALUES ('432')
select * from #MyTempTable
Update #MyTempTable
SET NAME = 'A' + REPLICATE('*', len(NAME) - 1)
select * from #MyTempTable
You should use replicate command.
Replicate- Repeats a string value a specified number of times.
Before update command,
NAME
3243
123
432423
423
432
After Update command,
NAME
A***
A**
A*****
A**
A**
Comments
try this
drop TABLE #TestTable
CREATE TABLE #TestTable (val varchar(500));
INSERT INTO #TestTable (val)
VALUES ('123'), ('23456'), ('1234555');
select case when len(isnull(val, ''))<2 then 'A' else 'A' + REPLICATE('*', len(val) - 1) end
from #TestTable
('1234', '123', '23456')is literally the value in a single record/column, then it would be very difficult to do this with basic SQL Server. Why isn't your data normalized?'1234', '123', '23456'is this a single column value or 3 Rows in your table??