WHERE to check if NVARCHAR record begins with a number or symbol in SQL

Question

I am using a SQL statement to fetch records where name begins with some alphabet

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

Can anyone suggest SQL query which will fetch Title beginning with numbers and symbols?

Answer 1

You can use LIKE with character sets:

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

Sample:

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