Hello
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?
thanks
+9
A:
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
2009-08-13 19:22:03
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
2009-08-13 19:24:23
@Dav: I wrote my additional text before you posted your comment, no plagiarism intended...
Roee Adler
2009-08-13 19:25:49
"?" is not a wildcard for LIKE predicates in Pervasive.
Bill Karwin
2009-08-13 19:29:48
Thanks Bill, fixed
Roee Adler
2009-08-13 19:33:57
+11
A:
Just use the "SUBSTRING" function :
SELECT * FROM "BOM_SUB_LEVEL" where SUBSTRING(TOP_CODE, 11, 1) = "_"
Marc
marc_s
2009-08-13 19:22:45
A:
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
2009-08-13 19:23:35
it's any combination, so LIKE "c%r" is also valid (and would match, say both car and czar
Cruachan
2009-08-13 19:25:01
A:
% is a wildcard and can replace an character, or combination of characters. Use ? instead which replaces a single character.
Cruachan
2009-08-13 19:23:41
+1
A:
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
2009-08-13 19:24:19
Pervasive uses backslash, not exclamation mark, to escape wildcards in LIKE patterns.
Bill Karwin
2009-08-13 19:31:37
A:
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
2009-08-13 19:25:14
+1
A:
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
2009-08-13 19:25:57