Replacing specific characters in a field

Question

Post reply

Replacing specific characters in a field

jfarrelly

Grasshopper

Points: 22
More actions
January 20, 2010 at 11:53 pm

#215747

Hi All - I'm a little new at this and trying to get the hang of Transact SQL
I have a large database where I needed to identify all the records in a table called TSRECORD where the field TITLE contained records with commonalities such as this
170_2456_03
So I used this command to pull this out
SELECT *
FROM TSRECORD
WHERE (title LIKE '170!_%%%%!_03%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%' ESCAPE '!')
The issue is that I need to replace all of the underscores with a forward slash so they end up looking like this. There are 1300 records to update.
170/2456/03
170/8976/03
etc
etc
Each number only has the fact that it starts with 170 and ends in 03
I have researched REPLACE AND TRANSLATE but can't find something that tells me how to do it when each record is different
Any help would be greatly appreciated 😀
Thanks. Joanne

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

-- make up some sample data

DROP TABLE #TSRECORD

CREATE TABLE #TSRECORD (title VARCHAR(30))

INSERT INTO #TSRECORD (title)

SELECT '170_2456_03' UNION ALL

SELECT '170_8976_03' UNION ALL

SELECT '170_21456_03' UNION ALL

SELECT '170_82976_03' UNION ALL

SELECT '170_222456_03' UNION ALL

SELECT '170_822976_03' UNION ALL

SELECT '171_2456_03' UNION ALL

SELECT '171_8976_03' UNION ALL

SELECT '170_2456_04' UNION ALL

SELECT '170_8976_04'

SELECT title FROM #TSRECORD

-- /make up some sample data

SELECT title, REPLACE(title, '_', '/') AS NewTitle

FROM #TSRECORD

WHERE LEFT(title,4) = '170_'

AND LEFT(REVERSE(title), 3) = '30_'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

arun.sas SSChampion Points: 11831 More actions · Answer 2

Chris Morris-439714 (1/21/2010)
SELECT title, REPLACE(title, '_', '/') AS NewTitle
FROM #TSRECORD
WHERE LEFT(title,4) = '170_'
AND LEFT(REVERSE(title), 3) = '30_'

or

SELECT title, REPLACE(title, '_', '/') AS NewTitle

FROM #TSRECORD

WHERE title like '170%03'

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

arun.sas (1/21/2010)
Chris Morris-439714 (1/21/2010)
SELECT title, REPLACE(title, '_', '/') AS NewTitle
FROM #TSRECORD
WHERE LEFT(title,4) = '170_'
AND LEFT(REVERSE(title), 3) = '30_'
or
SELECT title, REPLACE(title, '_', '/') AS NewTitle
FROM #TSRECORD
WHERE title like '170%03'

Much nicer 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jfarrelly Grasshopper Points: 22 More actions · Answer 4

Hi Chris

Thanks so much for your assistance. Greatly appreciated. That did the trick.

Thanks.

Jo