Vishwanath jawalkar Vishwanath jawalkar - 2 months ago 8
SQL Question

Regular Expression Pattern for Search in SQL

I want to search a table which has file name(s) with a {Numerical Pattern String}.PDF.

Example: 1.PDF, 12.PDF, 123.PDF 1234.PDF etc.....

select * from web_pub_subfile where file_name like '[0-9]%[^a-z].pdf'


But above SQL Query is resulting even these kind of files


1801350 Ortho.pdf
699413.processing2.pdf
15-NOE-301.pdf


Could any one help me what I am missing here.

vkp vkp
Answer

One way to do it is getting the substring before the file extension and checking if it is numeric. This solution only works well if there is only one . character in the file name.

select * from web_pub_subfile
where isnumeric(left(file_name,charindex('.',file_name)-1)) = 1

Note:

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).

To handle file names with mutliple . characters and if there is always a .filetype extension, use

select * from web_pub_subfile
where isnumeric(left(file_name,len(file_name)-charindex('.',reverse(file_name))-1)) = 1