SQLTeam.com | Weblogs | Forums

First character replace in sql server

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

hi hope this helps

SELECT 
  COALESCE(STUFF(productname, PATINDEX('%' + 'p' + '%', productname), LEN('p'), 'z'), 'z')
from 
  productdetails

image