Returning values from a database with a Substring

Question

Let's say I've got 3 product description fields with the values AC-120 XXX, AC-120,CCC and AC-120 BBB.

How would I get that information from a table using only AC-120 as my search argument?

I've tried using the subStr function but that won't return any values either

    SELECT TbArtikel.Artikel_Merk, TbArtikel.Artikel_Groep, TbArtikel.Artikel_Categorie_ID, TussenMAATenARTKEL.VoorraadNummer, TbArtikel.Artikel_ID, TussenMAATenARTKEL.ArtikelDetail_ID, TbArtikel.Artikel_Prijs_Advies, TbArtikel.Artikel_Prijs_Bees, TbArtikel.Artikel_Omschrijving
FROM TbArtikel INNER JOIN TussenMAATenARTKEL ON TbArtikel.Artikel_ID = TussenMAATenARTKEL.Artikel_ID
WHERE (((TbArtikel.Artikel_Merk)="Yonex") AND  ((TbArtikel.Artikel_Omschrijving)="%AC-102%"));

"I've tried using the subStr function" Share your code please. — Ilyes
– Ilyes, Commented Sep 13, 2019 at 8:19

Gordon Linoff · Accepted Answer · 2019-09-13 11:21:49Z

1

LIKE is very appropriate for this comparison. However, MS Access uses different wildcards from standard SQL. So you want:

TbArtikel.Artikel_Omschrijving) LIKE "*AC-102*"

In standard SQL, this would be:

TbArtikel.Artikel_Omschrijving) LIKE "%AC-102%"

answered Sep 13, 2019 at 11:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

daveyvdweide Over a year ago

Thank you, this is exactly what I was looking for :)

Goran Gajic · Accepted Answer · 2019-09-13 08:26:29Z

1

Try '((TbArtikel.Artikel_Omschrijving) LIKE "%AC-102%"' instead of '((TbArtikel.Artikel_Omschrijving)="%AC-102%"'

answered Sep 13, 2019 at 8:26

Goran Gajic

3033 silver badges13 bronze badges

4 Comments

Ilyes Over a year ago

What if 'AC-102' is in the end of the string?

Goran Gajic Over a year ago

w3schools.com/sql/sql_like.asp according to this site "%subString% Finds any values that have "subString" in any position"

daveyvdweide Over a year ago

Using LIKE "%AC-102%" did not work, it wouldn't display any information

Goran Gajic Over a year ago

This property has to be used on the where field, it should work, cause this is the way to do properly

Ilyes · Accepted Answer · 2019-09-13 08:39:27Z

0

Use MID() function as

MID(TbArtikel.Artikel_Omschrijving, 1, 6) = 'AC-120'

Instead of

TbArtikel.Artikel_Omschrijving ="%AC-102%"

answered Sep 13, 2019 at 8:39

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Collectives™ on Stack Overflow

Returning values from a database with a Substring

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related