Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Replace placeholders in Recordset and then Sort

Posted on 2003-12-01
4
Medium Priority
?
238 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 600 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

580 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