Solved

Replace placeholders in Recordset and then Sort

Posted on 2003-12-01
4
228 Views
Last Modified: 2012-06-27
Hi, I'm using ASP with MS SQL 2000.

I have a recordset that looks something like this. It has only one column, called Identity_Name:
  BCDE0001
  BCDE0002
  XXXX0001
  XXXX0002

For comparison purposes, I need to alter this recordset so that all the occurances of "XXXX" are replaced with another 4 character string. Then, the recordset needs to be re-sorted. Let's say that we change "XXXX" to "ABCD." The resulting recordset should look like this:
  ABCD0001
  ABCD0002
  BCDE0001
  BCDE0002

Does anyone know of a clever way to do this using SQL only? I'd rather not dump the recordset into an ASP array and then sort it.
0
Comment
Question by:tevelision
  • 3
4 Comments
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 200 total points
Comment Utility
UPDATE Table
SET Field = 'ABCD' + Right(Field,4)
WHERE LEN(Field) > 3
AND <You need a criteria to limit the fields that are updated>

Without a criteria it will update the whole lot. Do you have a criteria for the records that you want to update?



You can also sort on the fly, like so:


SELECT * FROM Table
ORDER BY 'ABCD' + Right(Field,4)
0
 

Author Comment

by:tevelision
Comment Utility
Thanks for the response, nmcdermaid.

I want to avoid using the UPDATE statement since I don't want to change what's in the database. So the "XXXX" have to stay in the database table. Sorry, I should have said this in the first message.

I think you're on to something with that sort on-the-fly statement. Let me give that whirl.
0
 

Author Comment

by:tevelision
Comment Utility
I found my answer:

SELECT     REPLACE(Identity_Name, 'XXXX', 'ABCD') AS Expr1
FROM        IdentityTable
ORDER BY Identity_Name

I'm still giving points to nmcdermaid for helping me realize that I can actually grab certain parts of a field. Thanks, man.
0
 

Author Comment

by:tevelision
Comment Utility
Sorrly, there's an error in the last line.

SELECT     REPLACE(Identity_Name, 'XXXX', 'ABCD') AS Expr1
FROM        IdentityTable
ORDER BY Expr1
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now