first character replace in sql server

Question

Hi I have one doubt in sql server.
how to replace only 1st character value when same character have multiple time.

CREATE TABLE [dbo].[productdetails](
[pid] [int] NULL,
[productName] varchar NULL
)
INSERT [dbo].[productdetails] ([pid], [productName]) VALUES (1, N'cinphol')
GO
INSERT [dbo].[productdetails] ([pid], [productName]) VALUES (2, N'apple')
GO
INSERT [dbo].[productdetails] ([pid], [productName]) VALUES (3, N'ppens')
GO
INSERT [dbo].[productdetails] ([pid], [productName]) VALUES (4, N'penrpos')
GO

based on above data I want output like below .
pid|productname
1 | cinZhol
2 |azple
3 |zpens
4 |zenrpos

select pid,replace(productname,'p','z')productname from productdetails

above query not giving expected result.
could you please tell me how to achive this task in sql server

Answer 1

Hi @harinathu ,

Welcome to Microsoft Q&A!

Please refer below:

select pid, SUBSTRING(productname,1,CHARINDEX('p',productname)-1) + 'z'+SUBSTRING(productname,CHARINDEX('p',productname)+1,len(productname)-CHARINDEX('p',productname)) productname  
from productdetails  

OR

SELECT pid,STUFF(productname, CHARINDEX('p', productname), 1, 'z') productname FROM productdetails  

Output:

pid productname  
1 cinzhol  
2 azple  
3 zpens  
4 zenrpos  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.