CODEWITHSUNDEEP
sqlselect
Kumar
Kumar

Reputation: 1666

SQL: Finding a value in Table

My DataBase Table look like:

 ID  | key | val_string | val_integer | val_timestamp
 ----------------------------------------------------
  1  |Name | name1      | NULL        | NULL
  1  |Age  | NULL       | 10          | NULL
  1  |DOB  | NULL       | NULL        | 11-06-2004 11:33:00
  2  |Name | name2      | NULL        | NULL
  2  |Age  | NULL       | 20          | NULL
  2  |DOB  | NULL       | NULL        | 10-06-1994 10:38:00

Now, my application takes input from the user and returns the ID matching to it.

For Example, if the user input is 10, then I should be able to find ID 1. Similarly if the user input is name2, I should be able to find ID 2.

The problem over here is, all the value field is of different data type ie. val_string is of string type, val_integer is of integer type and so on. But, we don't know the type for user input.

Upvotes: 1

Views: 67

Answers (2)

brummfondel
brummfondel

Reputation: 1210

You may add another varchar column "searchstring" that contains the data as string. Maybe a view is a better solution if the string is exactly the same as the data.

create view foo as select id,val_string from mytable where val_string is not null 
union select id,cast(val_integer as varchar) from mytable where val_integer is not null 
union select id,cast(val_timestamp as varchar) from mytable where val_timestamp is not null;

Upvotes: 1

Wolf
Wolf

Reputation: 170

Use Cast to convert your data types to string here is some links on CAST

http://www.1keydata.com/sql/sql-cast.html

http://msdn.microsoft.com/en-us/library/ms187928.aspx

hope this helps

if not let me know and il see if i can find another solution

Upvotes: 1

Related Questions