Using variable with query

Question

I have a field name authorisation having length 150 chars.

I want query to check authorisation at particular digit.

SELECT e.staffid, COUNT(e.staffid) FROM enrll e INNER JOIN staff s on e.staffid=s.staffid 
                        WHERE s.auth = '1' and NOT EXISTS (SELECT staffid FROM shdl h where s.staffid=h.staffid and h.shdldt='$unixdt')
                        GROUP BY e.staffid ORDER BY COUNT(e.staffid) DESC";

THough I have used a "1" for testing purpose but now I want to check auth at particular digit like. for that I took variable with 150 digit. I need to check at 150th position that if at last position it is 1 it will fetch record.

Jacob · Accepted Answer · 2011-08-23 07:29:32Z

1

... WHERE SUBSTRING(s.auth,150,1) = '1' ...

SUBSTRING(str, pos, len) can be used for this, but all your index on s.auth is useless then.

answered Aug 23, 2011 at 7:29

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Aditii Over a year ago

Substring is not a sql function than how can I use it with above code. Can you please rewrite the code.

Jacob Over a year ago

Substring is a MySQL function, just replace your WHERE s.auth = '1' with the code in my answer.

Aditii Over a year ago

OK.. what will happen if I use negative value like substr(s.auth,-1) = '1'

Jacob Over a year ago

-1 means get the last char, 150 just means get the 150th char. They are equivalent, if you have fixed size of 150 chars.

Andrej Over a year ago

But if not you will get empty string.

Andrej · Accepted Answer · 2011-08-23 07:32:40Z

1

May be you can try

substr(s.auth,-1) = '1'

answered Aug 23, 2011 at 7:32

Andrej

7,5041 gold badge22 silver badges22 bronze badges

1 Comment

Andrej Over a year ago

You can check working via query select substr('Testing',-1); Result is g.

Collectives™ on Stack Overflow

Using variable with query

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related