WHERE to check if NVARCHAR record starts with a number or character in SQL

Question

I use an SQL statement to retrieve records where the name begins with some alphabet

SELECT * FROM Music WHERE Title LIKE 'A%' ORDER BY Title 

Can someone suggest an SQL query that will retrieve a header starting with numbers and characters?

Answer 1

You can use LIKE with character sets:

 SELECT * FROM Music WHERE Title LIKE '[^A-Za-z]%' ORDER BY Title 

Example:

 declare @music table(id int identity(1,1) not null primary key, title varchar(10)) insert @music(title) values ('test1'), ('9test'), ('0test'), ('#test') SELECT * FROM @Music WHERE Title LIKE '[^A-Za-z]%' ORDER BY Title 

--- results ---

 id title 4 #test 3 0test 2 9test 

Answer 2

use PATINDEX

 SELECT * FROM Music WHERE PATINDEX('[^a-zA-Z]%', Title) = 1 ORDER BY Title 

• SQLFiddle Demo
• TSQL Doc: PATINDEX

Answer 3

You can do this using the PATINDEX function and the LEFT function in combination:

 SELECT * FROM Music WHERE PATINDEX('%[AZ ,az]%',LEFT(Title,1)) = 0 

Edit: since you start searching for a non-alphabet character at the beginning, change to = 0, = 1 will return everything starting with an alphabetic character.