CODEWITHSUNDEEP
sqlsql-server
JMD
JMD

Reputation: 19

Inserting space between the string (SQL Server)

I have a table called Manufacturers which contains a column Name with a data type of Varchar(100) that has 5761 entries such as XYZ(XYZ Corp).

I wish to insert a space between the Z and the (.

I have seen the STUFF and LEFT commands but can't seem to figure out if they apply to my scenario.

Upvotes: 0

Views: 1885

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

I would recommend:

update Manufacturers
    set name = replace(name, '(', ' (')
    where name like '%[^ ](%';

This will only update rows where there is not already a space before the open paren.

Note: If you have multiple open parens, it will update all of them. If that is an issue, ask a new question with appropriate sample data.

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can try the REPLACE (Transact-SQL) function as shown below.

update <yourTableName>
set <yourColumName> = replace(<yourColumName>, '(', ' ( ')
where <put the conditions here>

Here is an implementation to you.

create table test (Name varchar(20))
insert into test values ('XYZ(XYZ Corp)')

--selecting before update
select * from test

--updating the record
update test
set Name = replace(Name, '(', ' (')
where name like '%(%'  --Here you can add the conditions as you want to restrict the number of rows to be updated based on the available data or the patterns.   

--selecting after update
select * from test

Live Demo

Upvotes: 3

Related Questions