CODEWITHSUNDEEP
mysqlsqlwhere-clause
Yahoo
Yahoo

Reputation: 4187

SQL select Where Clause statement

I am quite sure its not possible but I would like to ask the experts here if its possible to write a SQL statement to 

Select * from ... Where [‘sent_to’]['bissness_id']=3516 .

If yes then what is the Syntax for it ? ( Please see the Picture below )

Select * from ... Where sent_to['bissness_id']=3516

Upvotes: 2

Views: 627

Answers (6)

Janine Rawnsley
Janine Rawnsley

Reputation: 1258

Not a very clear question..

If you have to cater for there being non "bissness_id" stuff in sent_to then you need to be explicit:

DECLARE @FilterID Varchar(10) = '"3516"'
Select * from ... Where sent_to LIKE '%'+@FilterID+'%'
                AND sent_to LIKE '%business_ids%'

If the column only has "bissness_id" then it's simpler

DECLARE @FilterID Varchar(10) = '"3516"'
Select * from ... Where sent_to LIKE '%'+@FilterID+'%'

Since in your example you have an integer on the where clause.. if this was intentional and part of the question then you can just use conversion:

DECLARE @FilterInt Int = 3516
DECLARE @FilterID Varchar(10) = '"' + CAST(3516 AS Varchar(10)) + '"'
Select * from ... Where sent_to LIKE '%'+@FilterID+'%'
                AND sent_to LIKE '%business_ids%'

Upvotes: 0

themightysapien
themightysapien

Reputation: 8379

i don' t think what u are asking can be done but what u can do this with simple trick

select * from yourtable where sent_to like '%5060%'

this will work getting u the rows with the business id 5060

u can choose to have separate table if u want more flexibility as per your requirement

Upvotes: 0

Alepac
Alepac

Reputation: 1831

If you have only businessIds on your sent_to column you could simply do:

Select * from ... Where sent_to LIKE '%"3516"%'

Upvotes: 0

Madhav
Madhav

Reputation: 2415

Try the below code

SELECT * FROM TABLE_NAME t1
WHERE t1.sent_to.business_ids = 3516;

Upvotes: 0

user2001117
user2001117

Reputation: 3777

this should work:

select * from web_query where sent_to like '%"business_ids":["3516"]%';

Upvotes: 0

Magnus
Magnus

Reputation: 980

Something like this would work:

select * from web_query where sent_to like '%"business_ids":["3516"]%';

or, alternatively:

select * from web_query where sent_to like '%"business_ids":[%"3516"%]%';

But you really shouldn't be storing JSON as a string value in a database column. It kind of defeats the purpose of a database on several levels.

EDIT: if you'd like to avoid changing the table structure but would still like to benefit from structural improvements to the database layout, you could look into writing some views that would parse sent_to into view columns.

Upvotes: 1

Related Questions