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

extracting info from LDAP column?

P: 25
I have a table with one of the columns being ldap info. Example value would be:
ou=FOO,ou=BAR,o=XYZ,dc=WORLD

From this I would like to extract the first string, in this case FOO.

My last attempt was:

Expand|Select|Wrap|Line Numbers
  1. SELECT 1+instr(T.ldap,"=") as startpos,
  2.               instr(T.ldap,",") as endpos,
  3.               mid(T.ldap,startpos,endpos-startpos)
  4. FROM myTable T where ldap like @likeStr;
which gives me error message:
ERROR 1054 (42S22): Unknown column 'startpos' in 'field list'


1+instr(T.ldap,"=") evaluates to the correct value -- 4 for the example ldap field value on the top
Same with instr(T.ldap,",") -- 7 in the example

And mid(T.ldap,4,7-4) would give me the correct value -- FOO in the example

However, I can not rely on the numbers 4 and 7.

Any suggestions?
Mar 10 '09 #1
Share this Question
Share on Google+
3 Replies


P: 30
Use substring() as shown below:

mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
Mar 12 '09 #2

mwasif
Expert 100+
P: 801
You can not use column aliases in functions, change your query as follows
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. mid(T.ldap,1+instr(T.ldap,"="), (instr(T.ldap,",")-1) + instr(T.ldap,"=")) 
  3. FROM myTable T where ldap like @likeStr;
Mar 12 '09 #3

P: 25
@mwasif
Thanks.
That's what I ended up doing. I just don't like the idea of the same expression being calculated twice.
Mar 12 '09 #4

Post your reply

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