Does This Shareware Exist

  • 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?

  • 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.

  • 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

  • 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...

  • 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.

  • 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.

  • 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-2 = replace(@s, char(13)+char(10), ''',''')

     end

    select @s-2

    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. 

     

     

     

     

     

     

  • Rick, that works, thank you.

  • 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