question

jairamj-2493 avatar image
0 Votes"
jairamj-2493 asked ·

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](100) 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

sql-server-generalsql-server-transact-sql
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

Hi @jairamj-2493,

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.


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @jairamj-2493,

Could you please provide any update? Thanks.

Best regards
Melissa

0 Votes 0 ·