SQL - Select integer in complex field (CAST and LIKE operators)

Question

I have a table TabCustomer containing a number of fields.

One of these fields is a complex one named externalUser:

{"id":123,"companyID":12,"name":"Paul","surname":"Smith",...}

How can I select all the rows of this tab containing 123 as id;

I tried with CAST this way:

 SELECT * from tabCustomer WHERE CAST(externalUser.id AS VARCHAR(6)) LIKE '%id:"123"%'

but getting 0 results

not exactly because for integer, it does not work the previous solution and I need it for integer as well — eeadev, Jun 24 '15 at 15:29

Rahul · Accepted Answer · 2015-06-24T15:20:56.873

1

Remove the " from LIKE operator. See a demo fiddle here http://sqlfiddle.com/#!5/1467a/4

LIKE '"id":123%'

Your query should look like

SELECT * from tabCustomer 
WHERE externalUser LIKE '"id":123%'

edited Jun 24 '15 at 15:20

answered Jun 24 '15 at 15:14

Rahul

thanks but my column looks like this {"id":123,"companyID":12,"name":"Paul","surname":"Smith"} with curly braces – eeadev Jun 24 '15 at 15:28
That should still work with little modification. See the fiddle again http://sqlfiddle.com/#!5/2a319/2 – Rahul Jun 24 '15 at 15:32
this is a kind of workaround though and it should not work if "id" is not in that position, right? – eeadev Jun 24 '15 at 15:37
1

It will work and you need to place the wildcard characters accordingly but it's never a elegent solution for the kind of data you have in that column. You should think re-designing your table to hold that data (looks like a JSON format data) properly. – Rahul Jun 24 '15 at 15:41

1 Answers1