By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,384 Members | 714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,384 IT Pros & Developers. It's quick & easy.

String Query Help

P: 1
I am trying to query a text string and remove everything other than 6 characters that will always be in the format DJ#### (as in the letters "DJ" and then 4 numbers). The problem I am having is that it can be anywhere in the text string.

What I currently have is the following SQL statement, but it doesn't account for any occurences with text before the DJ#### string. [ID-1490] is the Table name and [ref_notes] is the field with the text string in question. (I removed all the other fields I'm working with for simplicity sake).

SELECT Mid([ref_notes],1,6) AS test
FROM [ID-14090]
GROUP BY [ID-14090].ref_notes, Mid([ref_notes],1,6)
HAVING ((Mid([ref_notes],1,6)) Like "DJ####")

Any help would be greatly appreciated. Thanks.
--Rich
Oct 17 '06 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this:

SELECT Mid([ref_notes],InStr([ref_notes],"DJ",6) AS test
FROM [ID-14090]
GROUP BY test;


I am trying to query a text string and remove everything other than 6 characters that will always be in the format DJ#### (as in the letters "DJ" and then 4 numbers). The problem I am having is that it can be anywhere in the text string.

What I currently have is the following SQL statement, but it doesn't account for any occurences with text before the DJ#### string. [ID-1490] is the Table name and [ref_notes] is the field with the text string in question. (I removed all the other fields I'm working with for simplicity sake).

SELECT Mid([ref_notes],1,6) AS test
FROM [ID-14090]
GROUP BY [ID-14090].ref_notes, Mid([ref_notes],1,6)
HAVING ((Mid([ref_notes],1,6)) Like "DJ####")

Any help would be greatly appreciated. Thanks.
--Rich
Oct 17 '06 #2

Expert 5K+
P: 8,434
I am trying to query a text string and remove everything other than 6 characters that will always be in the format DJ#### (as in the letters "DJ" and then 4 numbers). The problem I am having is that it can be anywhere in the text string.

What I currently have is the following SQL statement, but it doesn't account for any occurences with text before the DJ#### string. [ID-1490] is the Table name and [ref_notes] is the field with the text string in question. (I removed all the other fields I'm working with for simplicity sake).

SELECT Mid([ref_notes],1,6) AS test
FROM [ID-14090]
GROUP BY [ID-14090].ref_notes, Mid([ref_notes],1,6)
HAVING ((Mid([ref_notes],1,6)) Like "DJ####")

Any help would be greatly appreciated. Thanks.
--Rich
Just a thought, haven't tested this. Plus, I haven't mastered the difference between HAVING and WHERE, so take the syntax with a grain of salt. But couldn't you use something like...

WHERE [ID-14090] LIKE "*DJ####*"

Also, though I have no experience with them, this sounds like a job for regular expressions. Does Access support them?

Perhaps this LIKE clause would be helpful combined with mmccarthy's response.
Oct 17 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.