Solved

Replace placeholders in Recordset and then Sort

Posted on 2003-12-01
4
235 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
[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 30

Accepted Solution

by:
nmcdermaid earned 200 total points
ID: 9851514
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
ID: 9851550
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
ID: 9851852
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
ID: 9851868
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

696 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