Vishwanath jawalkar Vishwanath jawalkar - 2 years ago 91
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

Could any one help me what I am missing here.

vkp vkp
Answer Source

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


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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download