CipherIS
asked on
SQL - Update part of a field
I have a field which I need to updated but it has a pipe so not the entire field can be updated.
Example (using dummy data)
Camaro | Corvette
Camaro | Corvette
Camaro | Corvette
Camaro
Camaro | Corvette
I need to update Camaro with Acura using a SQL statement.
Thanks.
Example (using dummy data)
Camaro | Corvette
Camaro | Corvette
Camaro | Corvette
Camaro
Camaro | Corvette
I need to update Camaro with Acura using a SQL statement.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here's how to insure that you only update the description to the left of a pipe, if a pipe exists:
UPDATE dbo.table_name
SET column_name = REPLACE(LEFT(column_name, CHARINDEX('|', column_name + '|')), 'Camaro', 'Acura') +
SUBSTRING(column_name, CHARINDEX('|', column_name + '|') + 1, 8000)WHERE description LIKE '%Camaro%'
UPDATE dbo.table_name
SET column_name = REPLACE(LEFT(column_name, CHARINDEX('|', column_name + '|')), 'Camaro', 'Acura') +
SUBSTRING(column_name, CHARINDEX('|', column_name + '|') + 1, 8000)WHERE description LIKE '%Camaro%'
ASKER
Thank You, works great.
Update <tbl>
set <field> = replace (<field>, 'Camaro', 'Acura')
-- where ?