SQL statement with LIKE

Question

I would like to select all records that have an underscore character in their 11th character, so i try this:

SELECT * FROM "BOM_SUB_LEVEL" where TOP_CODE like '%%%%%%%%%%_%%%'

but this doesnt work as expected, can someone help?

score 15 · Accepted Answer · answered Aug 13 '09 at 19:22

15

Just use the "SUBSTRING" function :

SELECT * FROM "BOM_SUB_LEVEL" where SUBSTRING(TOP_CODE, 11, 1) = "_"

Marc

answered Aug 13 '09 at 19:22

marc_s

675,133
158
1,253
1,388

1

+1 you beet me to it marc, I think this is the best (fastest) option. – northpole Aug 13 '09 at 19:26
1

+1. This makes way more sense than a whole bunch of single-character wildcards. – JamesMLV Aug 13 '09 at 19:30

Roee Adler · Answer 2 · 2009-08-13T19:34:20.163

11

For a single character wildcard use _. For multiple characters wildcards, use %. To escape a "real" appearance of _, use \_ (thanks Bill!).

Try the following code:

SELECT * FROM "BOM_SUB_LEVEL" where TOP_CODE like '___________\_%'

To further elaborate following Dav's comment, note that '%%%' is exactly the same as '%', since by definition '%' covers multiple characters.

edited Aug 13 '09 at 19:34

answered Aug 13 '09 at 19:22

Roee Adler

31,157
31
99
132

To elaborate on why this works and your original doesn't, chicane - the `%` wildcard is an "any number of characters" wildcard - so `%` is the same as `%%` is the same as `%%%` and so on because even a single one can match multiple characters. – Amber Aug 13 '09 at 19:24
@Dav: I wrote my additional text before you posted your comment, no plagiarism intended... – Roee Adler Aug 13 '09 at 19:25
"?" is not a wildcard for LIKE predicates in Pervasive. – Bill Karwin Aug 13 '09 at 19:29

score 2 · Answer 3 · answered Aug 13 '09 at 19:25

2

pervasive uses _ to match any single character and \_ to actually match an underscore.

so the select would be:

SELECT * FROM "BOM_SUB_LEVEL" where TOP_CODE like '___________\_%'

answered Aug 13 '09 at 19:25

gjutras

718
4
13

score 1 · Answer 4 · answered Aug 13 '09 at 19:24

1

LIKE % can mean any number of characters, use LIKE _ to mean just one. Since you're looking for an underscore, you need to escape it with !.

SELECT * FROM BOM_SUB_LEVEL WHERE TOP_CODE LIKE '__________!_%'

answered Aug 13 '09 at 19:24

JamesMLV

2,085
17
19

I missed the pervasive tag, this answer is for SQL Server – JamesMLV Aug 13 '09 at 19:29
Pervasive uses backslash, not exclamation mark, to escape wildcards in LIKE patterns. – Bill Karwin Aug 13 '09 at 19:31

score 0 · Answer 5 · answered Aug 13 '09 at 19:23

0

The % is not a per character wildcard, its a beginning and end of string wild card.

i.e. if I want to find all rows that have "car" in them, I would do this:

Select * from myTable where myCol LIKE '%car%'

If I wanted just the rows that STARTED with car:

Select * from myTable where myCol LIKE 'car%'

and ended with car:

Select * from myTable where myCol LIKE '%car'

answered Aug 13 '09 at 19:23

Neil N

23,912
15
82
141

it's any combination, so LIKE "c%r" is also valid (and would match, say both car and czar – Cruachan Aug 13 '09 at 19:25

score 0 · Answer 6 · answered Aug 13 '09 at 19:23

0

% is a wildcard and can replace an character, or combination of characters. Use ? instead which replaces a single character.

answered Aug 13 '09 at 19:23

Cruachan

15,379
5
57
109

score 0 · Answer 7 · answered Aug 13 '09 at 19:25

0

You can try something like: (play with the numbers, I don't have pervasive to test with)

SELECT * 
  FROM BOM_SUB_LEVEL 
 where SUBSTRING(TOP_CODE, 11,1) = '-'

answered Aug 13 '09 at 19:25

northpole

9,788
7
33
57

SQL statement with LIKE

7 Answers7