Data formatting

[Ivan]

Does anyone familiar with a tool or a quick way to put apostrophes and commas around some values, so they could be used in a query ? Example, if I have a bunch of zip codes such as:
10001
10003
10009
54321
89043
etc
Let's say I have a couple of hundreds of them. I need to be able to see them as -

'10001','10003','10009',&# 39;54321','89043' etc

Appreciate very much your time.

Ivan

[JustDoIt]

Use COALESCE.................

DECLARE @variable varchar(200)

SELECT @variable = COALESCE(@variable + ', ', '&#39 + variable
FROM tablename


SELECT @variable


obviously u can modify the coalesce line to suit your needs a bit more

------------
Ivan at 3/13/01 8:47:31 AM


Does anyone familiar with a tool or a quick way to put apostrophes and commas around some values, so they could be used in a query ? Example, if I have a bunch of zip codes such as:
10001
10003
10009
54321
89043
etc
Let's say I have a couple of hundreds of them. I need to be able to see them as -

'10001','10003','10009',&# 39;54321','89043' etc

Appreciate very much your time.

Ivan

[Steve Armistead]

This works, although each zip code is on a line by itself. It is still perfectly readable as input to a query. The number of apostrophes is important.


SELECT '''' + postal_code + '''' + '','' FROM your_table

Steve

------------
JustDoIt at 3/13/01 12:54:37 PM


Use COALESCE.................

DECLARE @variable varchar(200)

SELECT @variable = COALESCE(@variable + ', ', '&#39 + variable
FROM tablename


SELECT @variable


obviously u can modify the coalesce line to suit your needs a bit more

------------
Ivan at 3/13/01 8:47:31 AM


Does anyone familiar with a tool or a quick way to put apostrophes and commas around some values, so they could be used in a query ? Example, if I have a bunch of zip codes such as:
10001
10003
10009
54321
89043
etc
Let's say I have a couple of hundreds of them. I need to be able to see them as -

'10001','10003','10009',&# 39;54321','89043' etc

Appreciate very much your time.

Ivan

[Dale Shaw]

Hi

The big question is ... why do you want to do it? Is it to be used as input to another query? If so, you may save yourself trouble by creating a subquery....


Dale