CODEWITHSUNDEEP
sqlsql-serverssms
AYETY
AYETY

Reputation: 710

Replace the string characters in SQL Server

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.

Upvotes: 0

Views: 86

Answers (3)

Viswas Menon
Viswas Menon

Reputation: 310

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

Upvotes: 1

Veera
Veera

Reputation: 3492

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 )

Upvotes: 1

TheProvost
TheProvost

Reputation: 1893

Try it this way: 

  select replace(field1 + N' ' + field2 + N' ' + field3, 'CD ', '*') from
 table1

Upvotes: 0

Related Questions