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?
Asked
Active
Viewed 5,825 times
4
Walery Strauch
- 6,792
- 8
- 50
- 57
chicane
- 1,991
- 3
- 19
- 15
7 Answers
16
Just use the "SUBSTRING" function :
SELECT * FROM "BOM_SUB_LEVEL" where SUBSTRING(TOP_CODE, 11, 1) = "_"
Marc
marc_s
- 732,580
- 175
- 1,330
- 1,459
-
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
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.
Roee Adler
- 33,434
- 32
- 105
- 133
-
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
-
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 '___________\_%'
gjutras
- 736
- 1
- 4
- 13
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 '__________!_%'
JamesMLV
- 2,236
- 18
- 19
-
-
Pervasive uses backslash, not exclamation mark, to escape wildcards in LIKE patterns. – Bill Karwin Aug 13 '09 at 19:31
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'
Neil N
- 24,862
- 16
- 85
- 145
-
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
0
% is a wildcard and can replace an character, or combination of characters. Use ? instead which replaces a single character.
Cruachan
- 15,733
- 5
- 59
- 112
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) = '-'
northpole
- 10,244
- 7
- 35
- 58