SQL Select Where Column Begins with \

How do I select rows where a column value starts with a certain string?

You can do

select * from mytable where name like "Mr.%"

See http://www.sqlite.org/lang_expr.html

Select from column where value starts with 1

1) I want to select those rows where val1 starts with 1

SELECT
  *
FROM Table_1
WHERE val1 LIKE '1%'

2) rows where val1 starts with 19.

SELECT
  *
FROM Table_1
WHERE val1 LIKE '19%'

SQL select where column begins with \

The backslash \ can have special meaning in LIKE patterns. It's the default ESCAPE character in PostgreSQL or MySQL - but not in Oracle or SQL Server. (Can be modified with an added ESCAPE clause in subtly varying ways, follow link for your RDBMS.)

In addition to that, PostgreSQL used to interpret POSIX-style escapes in strings (a.k.a. "C escape syntax") before version 9.1 and MySQL still does in version 8.0). There you have to double \ to \\ to get an ordinary backslash.

According to standard SQL, \ is not a meta character in strings. PostgreSQL eventually switched to standard behavior and introduced a special escape-string-syntax with E'' and the config settings standard_conforming_strings and escape_string_warning to still allow escaped string when needed.

Since Postgres 9.1 standard_conforming_strings = on by default. So you write:

... col LIKE '\\%'    -- double once to get 1 literal backslash in LIKE pattern

Else you have to write:

... col LIKE E'\\\\%'  -- double twice to also disable special meaning as escape char

---

On top of that, \ also has special meaning in many client programs and programming languages. If strings get interpreted before they even reach the database engine, you may have to double the \ another time (or escape it in some other fashion). See this related question, for instance.

SQL select where column begins with Letters

Most databases support left(), so you can do something like this:

select id,
       (case when left(time, 1) between 'a' and 'z' or left(time, 1) between 'A' and 'Z'
             then SSS else TIN
        end) as Legal_Doc_no
from tbl1;

Depending on the database, there might be other solutions.

In SQL Server, you can do:

select id,
       (case when time like '[a-z]%'
             then SSS else TIN
        end) as Legal_Doc_no
from tbl1;

If you have a case-sensitive collation, then you'll need to take that into account:

select id,
       (case when lower(time) like '[a-z]%'
             then SSS else TIN
        end) as Legal_Doc_no
from tbl1;

Select rows where column value starts with a string AND ends with an even number

You could use substr to extract the last three characters, to_number to treat them as a number, and then mod it by 2 to see if it's even:

SELECT *
FROM   mytable 
WHERE  mycolumn LIKE 'ABD%' AND MOD(TO_NUMBER(SUBSTR(mycolumn, -3)), 2) = 0

Display row where string column starts with specific letter

Use single quotes! Double quotes are meant for identifiers only (such as column names, table names, and so on), hence the error you are getting.

On the other hand, single quotes stand for literal strings, which is what you mean here:

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE 'A%';

In that regard, Oracle strictly follows the ANSI SQL specification. There are other databases that allow double quotes for strings, and use other characters to quote identifiers (MySQL for example)... but I personally find that it makes things more confusing that they would need to.

SQL query, where name in column begins with a certain letter; clarification

If you are using SQL Server, you can do:

WHERE Name like 'M[a-m]%' 

However, a generate range, such as My-No, would be harder to express. You can always do:

WHERE name >= 'Ma' and name < 'Mn'

as well. This works for more complex ranges.

SQL like search string starts with

SELECT * from games WHERE (lower(title) LIKE 'age of empires III');

The above query doesn't return any rows because you're looking for 'age of empires III' exact string which doesn't exists in any rows.

So in order to match with this string with different string which has 'age of empires' as substring you need to use '%your string goes here%'

More on mysql string comparision

You need to try this

SELECT * from games WHERE (lower(title) LIKE '%age of empires III%');

In Like '%age of empires III%' this will search for any matching substring in your rows, and it will show in results.

Select records based on what a field begins with?

    SELECT Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanation, Count(tblComorbidity.comorbidityexplanation) AS CountOfcomorbidityexplanation
FROM tblKentuckyCounties INNER JOIN (tblComorbidity INNER JOIN (Person INNER JOIN tblComorbidityPerson ON Person.PersonID = tblComorbidityPerson.personID) ON tblComorbidity.ID = tblComorbidityPerson.comorbidityFK) ON tblKentuckyCounties.ID = Person.County
GROUP BY Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanation
HAVING (Person.spineinjuryAdmit LIKE "c*");

---

Related Topics