Solved

Replace placeholders in Recordset and then Sort

Posted on 2003-12-01
4
233 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Rebooting Witness SQL Server 2 25
removing unwanted rows from an sql server ranked table 13 36
IF SQL Query 12 29
Linked Server - SP with Param to VIew 7 22
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

749 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