If I have string like
select field1 + N' ' + field2 + N' ' + field3 from table1
how to make so that I could change or replace the characters starting from third place to fifth?
I mean if select statement above returns a string ABCD EFGH IJKL, then I want to replace CD(space) with *
Thanks.
Use the stuff function:
SELECT STUFF('ABCD EFGH IJKL', 3, 3, '*');
select STUFF(field1 + N' ' + field2 + N' ' + field3, 3, 3, '*') from table1
Syntax:
STUFF ( character_expression , start , length , replaceWith_expression )
I feel STUFF function is the best way to go about it.
Below is a small example on how i would approach the same
CREATE TABLE TESTDATA
(
DATA NVARCHAR(MAX),
DATA1 NVARCHAR(MAX),
DATA2 NVARCHAR(MAX)
)
INSERT INTO TESTDATA VALUES('ABCD','HGHG','HGFD')
INSERT INTO TESTDATA VALUES('HHGG','ADFS', 'ERET')
INSERT INTO TESTDATA VALUES('JKJK','GGHG', 'TRED')
DECLARE @DATA TABLE(DATA VARCHAR(100))
INSERT INTO @DATA select STUFF(DATA + N' ',3,3,'*') + DATA1 + N' ' + DATA2 from TESTDATA
SELECT * FROM @DATA
The Result for the same would be,
AB*HGHG HGFD
HH*ADFS ERET
JK*GGHG TRED
I hope the above was useful
Try it this way:
select replace(field1 + N' ' + field2 + N' ' + field3, 'CD ', '*') from
table1
stuff function