Categories
SQL

Replacing first occurrence of certain characters in MSSQL database

This post is regarding how to replace first occurrence of one or more characters from database table Column.

Suppose we have table named User with columns Id, Name, Phone Number as shown in the figure below.

User database table
User database table

And contains records as shown in figure below:

User records before script
User records before script

Now what I required to accomplish is replace the first ‘-‘ character with space ‘ ‘ so that for example phone number 214-654-1800 becomes 214 654-1800. The script for this as below and next to it is the resulting data.

MS Sql Script:

DECLARE @find varchar(8000)
SELECT @find='-' -- << Replace character
UPDATE [User]
SET [PhoneNumber]=Stuff([PhoneNumber], CharIndex(@find, [PhoneNumber]), Len(@find), ' ') -- << Replacement character

After applying the script the changes happen as in figure below:

User records after applying script
User records after applying script

 

Reference link: Is it possible to replace the first occurrence of a string in a column ?

 

One reply on “Replacing first occurrence of certain characters in MSSQL database”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site supports SyntaxHighlighter via WP SyntaxHighlighter. It can highlight your code.
How to highlight your code: Paste your code in the comment form, select it and then click the language link button below. This will wrap your code in a <pre> tag(or shortcode) and format it when submitted.

This site uses Akismet to reduce spam. Learn how your comment data is processed.