• 0

SQL Wildcard on a Varible


Question

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

 

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

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?

Link to comment
Share on other sites

  • 0

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? 

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0

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";
Link to comment
Share on other sites

  • 0

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
Link to comment
Share on other sites

  • 0

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

Link to comment
Share on other sites

  • 0

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

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.