Select Values That Begin with a Number

Select values that begin with a number

SELECT * FROM YourTable WHERE YourColumn regexp '^[0-9]+'

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%'

determine if column value string starts with a number

SELECT CASE WHEN ISNUMERIC(SUBSTRING(LTRIM(Description), 1, 1)) = 1 
         THEN 'yes' 
         ELSE 'no' 
       END AS StartsWithNumber
FROM Questions 

• ISNUMERIC
• SUBSTRING

Select Query | Select Entires That Don't Start With A Number - MySQL

Try this:

SELECT DISTINCT name, location FROM object
       WHERE substring(location, 1, 1)
                  NOT IN ('1','2','3','4','5','6','7','8','9');

or you have to add NOT LIKE before every number:

SELECT DISTINCT name, location FROM object
       WHERE location NOT LIKE '1%'
          OR location NOT LIKE '2%'
          ...

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

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

MySQL how to check if value starts with letter and ends with a number?

You should use REGEXP, WHERE fields REGEXP regex_string. Also, you don't have to write all of the alphabet letter, you could just write it like [a-zA-Z].

To be specific: a range is a contiguous series of characters, from low
to high, in the ASCII character set.[101] For example, [z-a] is not a
range because it's backwards. The range [A-z] matches both uppercase
and lowercase letters, but it also matches the six characters that
fall between uppercase and lowercase letters in the ASCII chart: [, \,
], ^, _, and '.

https://docstore.mik.ua/orelly/unix3/upt/ch32_08.htm

SELECT * FROM hotelroom
WHERE city LIKE '%Cansas%' AND
key REGEXP '^[a-zA-Z].*[0-9]$'

Also,

I want to show specific data where all the values start with a letter
and ends with a number in the city of cansas only.

If you only want to get the result from the city of Cansas, you don't have to use LIKE. If you're using LIKE, it will also match Cansas2 city or anything that has Cansas as it's substring.

You could just use equals (=) operator.

SELECT * FROM hotelroom
WHERE city = 'Cansas' AND
key REGEXP '^[a-zA-Z].*[0-9]$'

---

Related Topics