Link to home
Start Free TrialLog in
Avatar of JMO9966
JMO9966

asked on

Select query help needed

I have a field that I query that is a Text field that stores three value all of which will either be a zero or a one.

111
101
010
001
100

You get the idea.  I need to write a MSAccess and a SQL query to return any records that have the second value set to a 1.  In the example above, this would return two records (the first and third records listed).

Can someone give me a hand on the syntax for this query in MS Access and SQL?

Thanks,
JMO9966
Avatar of mbizup
mbizup
Flag of Kazakhstan image

SELECT *
FROM YourTable
WHERE YourField LIKE "*1*
Need a closing quote:
SELECT *
FROM YourTable
WHERE YourField LIKE "*1*"


select <column_name> from <table_name> where substring(<column_name>, 2, 1) = 1
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello JMO9966,

SELECT *
FROM YourTable
WHERE YourField LIKE '_1%'



Aneesh R
>stores three value
True... I'm assuming that the field length is exactly three characters, where the * would work.  If it is longer and a test for numeric characters is needed, this could be used too:

 WHERE YourField LIKE "#1#"

I'm also assuming that the back-end is Access.
I don't know if your field is numeric or alphanumeric but the query will work either way. For MS-SQL will be:

SELECT
      *
FROM
      YourTable
WHERE
      substring(cast(YourFiled AS varchar),2,1)='1'