{"id":619,"date":"2013-06-03T13:54:21","date_gmt":"2013-06-03T08:24:21","guid":{"rendered":"http:\/\/www.harshbaid.in\/?p=619"},"modified":"2013-06-03T13:54:21","modified_gmt":"2013-06-03T08:24:21","slug":"replacing-first-occurrence-of-certain-characters-in-mssql-database","status":"publish","type":"post","link":"https:\/\/www.harshbaid.in\/2013\/06\/03\/replacing-first-occurrence-of-certain-characters-in-mssql-database\/","title":{"rendered":"Replacing first occurrence of certain characters in MSSQL database"},"content":{"rendered":"

T<\/span>his post is regarding how to replace first occurrence of one or more characters from database table Column.<\/p>\n

Suppose we have table named User with columns Id, Name, Phone Number as shown in the figure below.<\/p>\n

\"User<\/a>
User database table<\/figcaption><\/figure>\n

And contains records as shown in figure below:<\/strong><\/p>\n

\"User<\/a>
User records before script<\/figcaption><\/figure>\n

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

MS Sql Script:<\/strong><\/p>\n

DECLARE @find varchar(8000)\r\nSELECT @find='-' -- << Replace character\r\nUPDATE [User]\r\nSET [PhoneNumber]=Stuff([PhoneNumber], CharIndex(@find, [PhoneNumber]), Len(@find), ' ') -- << Replacement character<\/pre>\n
After applying the script the changes happen as in figure below:<\/strong><\/p>\n
\"User<\/a>
User records after applying script<\/figcaption><\/figure>\n
 <\/p>\n
Reference link:<\/strong>\u00a0Is it possible to replace the first occurrence of a string in a column ?<\/a><\/p>\n
Related articles<\/h6>\n