CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Imran Zain
Imran Zain

Reputation: 473

How to replace nth character in sql server

I am trying to replace the nth character in SQL Server. I tried with it using replace():

SELECT REPLACE(ABC,0,1)  FROM XXX

In above code all zeros will be replaced with one, but I only want to change it in a particular position and sometimes that position can change.

Upvotes: 35

Views: 85592

Answers (6)

Ajay Kumar
Ajay Kumar

Reputation: 11

I had a table with a PartDesc column, as below, where I need to delete the characters starting from index 1 until the - character.

PartDesc
OPERA_BLUE-ALTROZ DCA XZ 1.2 RTN BS6
ATLAS_BLACK-NEXON XZ+ DK 1.2 RTN BS6
DAYTONA_GREY-PUNCH ADV 1.2P BS6 MT RT
ARCADE_GREY-ALTROZ XZ+ 1.2 RTN BS6
CALGARY_WHTE-NEXON XM(S) 1.2 RTN BS6

Here, after using the below query, I got the desired result:

Select PATINDEX('%-%',PartDesc),Stuff(PartDesc,1,PATINDEX('%-%',PartDesc),'' ),* from #temp

Upvotes: 0

asontu
asontu

Reputation: 4659

You use STUFF for this:

SELECT STUFF(ABC, 5, 1, '1')
FROM XXX

This would replace the 5th character with a 1.

Upvotes: 13

I A Khan
I A Khan

Reputation: 8841

use stuff The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

select STUFF(ABC, starting_index, 1, 'X') from XXX

"Here your int position to replace" is the position no just replace with any int no and that position will be replaced Note : (Thanks to pcnate for suggestion) starting_index is your int position to replace.

Upvotes: 42

A_Sk
A_Sk

Reputation: 4630

Use Stuff.

STUFF(Column_Name,starting_index,
lenth_ofthestring_to_replace_from_starting_index, character_to_replce)

Example_

DECLARE @str varchar(100) = '123456789'
select @str
SELECT STUFF(@str,2,1, 'hello') 
-- want to replece 1 charter starting from 2nd position with the string 'hello'

Check this.

 SELECT STUFF(@str,2,25, 'hello'),len(@str)

Upvotes: 6

lc.
lc.

Reputation: 116458

You're looking for STUFF:

select STUFF(ABC, @n, 1, 'X') from XXX

This would replace the @nth character with an X.

Technically it seeks into the original string at column ABC starting at position @n, deletes 1 character, then inserts the string 'X' at that position.

Upvotes: 32

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Use stuff():

select stuff(abc, 0, 1, 'a')

It is documented here.

Upvotes: 8

Related Questions