Jump to content



Photo

SQL Wildcard on a Varible


  • Please log in to reply
7 replies to this topic

#1 add13ct

add13ct

    Neowinian

  • Joined: 17-November 11

Posted 26 July 2013 - 14:42

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

 




#2 +Majesticmerc

Majesticmerc

    Resident Idealist

  • Tech Issues Solved: 8
  • Joined: 24-August 05
  • Location: United Kingdom
  • OS: Arch Linux / Win 7
  • Phone: HTC One X

Posted 26 July 2013 - 21:08

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?



#3 Original Poster

Original Poster

    C++ n00b

  • Tech Issues Solved: 1
  • Joined: 15-July 08
  • Location: my room
  • OS: windows 7, backtrack 5, OSx 10.6

Posted 28 July 2013 - 04:29

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? 



#4 +virtorio

virtorio

    4089 III

  • Tech Issues Solved: 11
  • Joined: 28-April 03
  • Location: New Zealand
  • OS: OSX 10.9, Windows 8.1
  • Phone: Samsung Galaxy SIII

Posted 28 July 2013 - 04:40

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.



#5 Matthew_Thepc

Matthew_Thepc

    Neowinian

  • Joined: 16-July 11
  • Location: San Jose, CA
  • OS: Windows 8.1 RTM

Posted 28 July 2013 - 05:03

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";


#6 Squirrelington

Squirrelington

    Squirrelies!

  • Tech Issues Solved: 1
  • Joined: 26-December 02
  • Location: Oshkosh, WI, USA
  • OS: Windows 8.1 64-bit Enterprise
  • Phone: HTC One X (GSM (International) Tegra 3 - S720e / HTC__203) - AT&T - 4.2.2

Posted 28 July 2013 - 05:12

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


#7 buchman

buchman

    Neowinian

  • Joined: 25-July 05

Posted 30 July 2013 - 16:26

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



#8 Squirrelington

Squirrelington

    Squirrelies!

  • Tech Issues Solved: 1
  • Joined: 26-December 02
  • Location: Oshkosh, WI, USA
  • OS: Windows 8.1 64-bit Enterprise
  • Phone: HTC One X (GSM (International) Tegra 3 - S720e / HTC__203) - AT&T - 4.2.2

Posted 31 July 2013 - 05:51

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