CODEWITHSUNDEEP
sqlsql-server
Flood Gravemind
Flood Gravemind

Reputation: 3803

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

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?

Upvotes: 4

Views: 4497

Answers (3)

Chris Gessler
Chris Gessler

Reputation: 23113

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

Upvotes: 6

Hart CO
Hart CO

Reputation: 34774

Can do this with PATINDEX function and LEFT Function combined:

SELECT *
FROM Music
WHERE PATINDEX('%[A-Z ,a-z]%',LEFT(Title,1)) = 0

Edit: Since you're searching for non-alphabet character at start, changed to = 0, = 1 would return any that start with alphabetic character.

Upvotes: 0

John Woo
John Woo

Reputation: 263733

use PATINDEX

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

Upvotes: 2

Related Questions