• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

sql question

I need an SQL that will change all rows of a field of a table that ends in 'CC' to 'C'

Start with this:
D215CC
End with this:
D215C
0
thenrich
Asked:
thenrich
  • 3
1 Solution
 
Brendt HessSenior DBACommented:
This will do it:

UPDATE MyTable
SET MyField = Left(MyField, Len(MyField)-1)
WHERE MyField LIKE '%CC'

Test by running this SELECT:

SELECT MyField, Left(MyField, Len(MyField)-1)
FROM MyTable
WHERE MyField LIKE '%CC'
0
 
thenrichAuthor Commented:
What would I do if I wanted to 'CT' to 'P' ?
0
 
Brendt HessSenior DBACommented:
Something like this:

UPDATE MyTable
SET MyField = Left(MyField, Len(MyField)-2) + 'P'
WHERE MyField LIKE '_%CT'

Test by running this SELECT:

SELECT MyField, Left(MyField, Len(MyField)-2) + 'P'
FROM MyTable
WHERE MyField LIKE '_%CT'

NOTE:  The underscore is a tweak that ensures it is not replacing a record with only 'CT' in the field with a value of 'P'.
0
 
Brendt HessSenior DBACommented:
You also have an alternate possibility using the STUFF command.  This version works for any arbitrary value and replacement, and uses variables to hold the search and replace values:

DECLARE @SearchFor varchar(10)
DECLARE @ReplaceWith varchar(10)

SET @SearchFor = 'CT'
SET @Replace = 'P'

UPDATE MyTable
SET MyField = STUFF(MyField, Len(MyField) - Len(@SearchFor), Len(@SearchFor), @Replace)
FROM MyTable
WHERE MyField LIKE '%' + @SearchFor

Test this with the code:


DECLARE @SearchFor varchar(10)
DECLARE @ReplaceWith varchar(10)

SET @SearchFor = 'CT'
SET @Replace = 'P'

SELECT MyField, STUFF(MyField, Len(MyField) - Len(@SearchFor), Len(@SearchFor), @Replace)
FROM MyTable
WHERE MyField LIKE '%' + @SearchFor
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now