August 29, 2006 at 1:26 pm
At the company that I used to work for someone developed a windows app that I could drop a column of data into and it would format it for me to be able to use in a where clause. That was properity for the company so I obviously was not able to take it with me, where can I find someting that out in the real world?
August 29, 2006 at 1:30 pm
Can you show us a before and after of the column and also how it was used in the where clause? It's surely something when can rebuild.
August 29, 2006 at 2:09 pm
Lets say that I have the following order numbers that I want to lookup in our favorite database, Northwind:
10248
10249
10250
10251
10252
10253
10254
10255
10256
10257
I want to put this in the following format or somthing like it to drop into a select statment:
'10248',
'10249',
'10250',
'10251',
'10252',
'10253',
'10254',
'10255',
'10256',
'10257'
The select statement would look like:
Select * from Northwind.dbo.orderdetail
where orderid in ('10248',
'10249',
'10250',
'10251',
'10252',
'10253',
'10254',
'10255',
'10256',
'10257')
Right now I am dropping my columns into Excel, formatting them there and then dropping them back into SQL, I know that there has to be a better way.l
August 29, 2006 at 2:21 pm
Well first, you don't need to cnovert those to strings, but I'll go along with the exemple.
Why can't you do something like : Select * from Table where OrderID IN (Select Orderid from tableb where ...)
That could also be written as a join but anyways...
August 29, 2006 at 2:24 pm
In this case, yes, but the challenge that I am facing is that I freqently get data from an outside source, rather than import it to a table and join to that table, I do the filter as stated above, that was the point of the application.
August 29, 2006 at 2:28 pm
I guess it simply selected the data from the outside source nad then concatenated apostrophes and commas. it's nothing you can't handle by yourself if you ever decided to rewrite something like that.
August 29, 2006 at 3:16 pm
This could be fine tuned a little more, but create an SP like this:
create procedure sp_quotes
(@s varchar(200))
as
begin
while charindex(char(13)+char(10),@s) > 0
begin
set @S = replace(@s, char(13)+char(10), ''',''')
end
select @S
end
To use:
exec sp_quotes
'
aaaa
bbbb
cccc
ddddd
eeeee
fffff
'
and you would get a result that looks like this:
','aaaa','bbbb','cccc','ddddd','eeeee','fffff','
which has a few extra commas and quotes on either end, but hey - it's free! Then you can cut/paste and put this in your query.
August 30, 2006 at 4:45 am
Rick, that works, thank you.
August 31, 2006 at 8:33 am
That is pretty cool, Rick. Thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply