Selecting and replacing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting and replacing

Author

Topic

kaleidoscope
Starting Member

5 Posts

Posted - 2009-01-13 : 15:17:44

Hi,
Is there a way to do a SELECT statement based on criteria and in the same query replace some values in a column based on another set of criteria. For example:

SELECT Name, Address FROM ADDRESS_BOOK
WHERE City = 'Montreal';

In this same query, what to do if I want to change the values of the column address so that I replace the numbers in the column address as follows:

1 becomes A and 2 becomes B?

I was able to do it but using 2 separate queries (in the second query I used the UPDATE and SET statements)
Any help is greatly appreciated!

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-13 : 15:35:15

UPDATE T
SET T.ADDRESS_BOOK = REPLACE(REPLACE(T.ADDRESS_BOOK, '1', 'A'), '2', 'B')
FROM ADDRESS_BOOK T
WHERE T.CITY = 'Montreal'

kaleidoscope
Starting Member

5 Posts

Posted - 2009-01-13 : 15:46:20

quote:
Originally posted by rohitkumar

UPDATE T
SET T.ADDRESS_BOOK = REPLACE(REPLACE(T.ADDRESS_BOOK, '1', 'A'), '2', 'B')
FROM ADDRESS_BOOK T
WHERE T.CITY = 'Montreal'

Thanks, but this query does not return a result. It's an UPDATE query so another separate SELECT clause is needed. Can we combine both so that the query starts with SELECT?

singularity
Posting Yak Master

153 Posts

Posted - 2009-01-13 : 18:46:57

SELECT Name, REPLACE(REPLACE(ADDRESS, '1', 'A'), '2', 'B') AS Address
FROM ADDRESS_BOOK
WHERE City = 'Montreal';

kaleidoscope
Starting Member

5 Posts

Posted - 2009-01-13 : 18:53:28

Good alternative. Thanks & cheers (sorry, ramping up!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources