Sign in to follow this  
Followers 0
add13ct

SQL Wildcard on a Varible

8 posts in this topic

Hi all

 

Say I have one variable which a user enters a unique identifier into which can be for a Incident, Change or Help Request but Incidents, Changes and help Requests are held in different tables.

 

Is it possible to get my query to look at the specific table defined by the first 3 letters of my variable.

 

Thanks

 

Share this post


Link to post
Share on other sites

Can you give an example of what you're trying to achieve? What would your ideal code look like? Is the code SQL or something that creates an SQL query and runs it?

Share this post


Link to post
Share on other sites

Hi all

 

Say I have one variable which a user enters a unique identifier into which can be for a Incident, Change or Help Request but Incidents, Changes and help Requests are held in different tables.

 

Is it possible to get my query to look at the specific table defined by the first 3 letters of my variable.

 

Thanks

im sure its really easy but need more details!?! if its a specific table then the mention of a wild card in your title confuses me? 

Share this post


Link to post
Share on other sites

Seems like it would be much easier to just use a different query for each table. 

You want to doing this at the point of building/generating the query, not as part of the query.

Share this post


Link to post
Share on other sites

I think he means something like (and sorry if my SQL is bad, I haven't used it much recently):

 

SELECT * FROM Cha* WHERE x == y

 

so instead of specifying the whole table name "Changes," he just wants to put "Cha*" (the first three letters, then a wildcard).

 

As was said above, there's probably an easy way to do it, but, if you want a sure-fire solution, why don't you just do a case switch over the table name?

$table = "Cha";

switch($table) {
    case "Cha":
        $table = "Changes";
    break;
etc. etc. etc.
}

$Sql = "SELECT * FROM " . $table . " WHERE x == y";

Share this post


Link to post
Share on other sites

short of that, you could do this in sql as well by using cursors.

 

This will work for Microsoft SQL, not sure what the format would be for other sql engines.

declare table_field_cursor cursor for select name from sys.tables where name like 'Cha%'
open table_field_cursor
declare @table_name varchar(1000)

fetch next from table_field_cursor into @table_name
while @@fetch_status = 0
begin

-- WORK GOES HERE
select * from @table_name where x = y

fetch next from table_field_cursor into @table_name
end

close table_field_cursor
deallocate table_field_cursor

Share this post


Link to post
Share on other sites

INFORMATION.SCHEMA would probably be more compatible than sys.tables also why not use a substring rather than a like and should it only be only one table? It would save on a cursor then

DECLARE @id VARCHAR(256)

DECLARE @Nameoftable SYSNAME
SET @Nameoftable = 'billak22'

SET @Nameoftable = SUBSTRING(@Nameoftable, 1, 3)

IF EXISTS ( SELECT  *
            FROM    INFORMATION_SCHEMA.tables
            WHERE   TABLE_NAME = @Nameoftable ) 
    BEGIN
        EXEC ('select * from ' + @Nameoftable + ' with (nolock)')
    END
ELSE 
    RAISERROR ('Table does not exist',11,1) WITH NOWAIT

Share this post


Link to post
Share on other sites

'Is it possible to get my query to look at the specific table defined by the first 3 letters of my variable.'

 

That is why i am using a cursor, so i can query sys.tables to get a list of all tables where the name column matches his first 3 letters and then performs a select on that table with his variable (which he would just need to enter in).

 

Your solution would not do that, it just defines the table in a variable and does not support wildcard table names or iterating though a list of tables based on a wildcard.

 

I probably could use INFORMATION_SCHEMA.tables though.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.