Solved

sql question

Posted on 2008-10-03
4
199 Views
Last Modified: 2010-03-19
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
Comment
Question by:thenrich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 22637287
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
 
LVL 5

Author Comment

by:thenrich
ID: 22637391
What would I do if I wanted to 'CT' to 'P' ?
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 22637432
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
 
LVL 32

Expert Comment

by:bhess1
ID: 22637470
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question