CODEWITHSUNDEEP
sqlms-access
user3285482
user3285482

Reputation: 3

SQL Query to check if value in one field is contained in same row

I have a table with vehicle records it has a distinct numberplate then an image path to the record is placed.

----------------------------------------------------------------------
| Number Plate                                   |          ImagePath
----------------------------------------------------------------------

TCZ9352                                            \folder\TCZ9352.pdf#

PCZ9353                                            \folder\\1-PB2CZ-2-PCZ9353.pdf#

I would like to do a query which will check the numberplate field and see if it is contained in the imagepath name to ensure that the records are indeed pointing to a valid image.

Upvotes: 0

Views: 1324

Answers (2)

fthiella
fthiella

Reputation: 49049

For MySQL you could use this:

SELECT
  number_plate,
  imagepath,
  CASE WHEN imagepath LIKE CONCAT('%', number_plate, '%')
       THEN 'Yes'
       ELSE 'No'
  END AS is_contained
FROM
  yourtable

For Ms-Access:

SELECT
  number_plate,
  imagepath,
  IIF(InStr(imagepath, number_plate)>0, 'Yes', 'No') AS is_contained
FROM yourtable

or this to return only the rows that does not contain the string:

SELECT
  number_plate,
  imagepath
FROM
 yourtable
WHERE
  InStr(imagepath, number_plate)=0

Upvotes: 4

Tom Haws
Tom Haws

Reputation: 1342

SELECT * FROM vehicle
WHERE imagepath NOT LIKE CONCAT('%', numberplate, '%')
;

Upvotes: 0

Related Questions