Searching number 1-5 in a text using PATINDEX in SQL 1

[burakcan]

Hello,

i am trying to search a number 1-5 in a long text field.
I am using PATINDEX function and regular expression.
here is my example:
Declare @mystr varchar (50)
set @mystr = 'closing in 5 days'

Select substring(@mystr,patindex ('%[1-5]%'),@mystr),1) =' '
if text like
'closing in 5days' or 'closing in5 days'
then i would like to catch it but i dont want to catch text like
'closing in 15 days'

Select substring(@mystr,patindex ('%[1-5]%'),@mystr),1) =' '
this script will catch if the text contain 'closing in 5 days' or 'closing in5 days' but it would not catch 'closing in 5days' because after 5 there is a alfanumeric character.
Is there way to use Patindex and regular expression to solve this problem.
Note I need to use this script in sql function then i believe i cannot use regex.
Any help greatly appriciated.
thx

 

[gmmastros]

I encourage you to read this blog that I wrote:

http://blogs.lessthandot.com/index.php/datamgmt/datadesign/extracting-numbers-with-sql-server/

In that blog, I present a function that will extract the numbers from a string. Once extracted, you can further check that it's within range.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

For example....

Declare @Temp Table(Data VarChar(50))

Insert Into @Temp Values('closing in 5 days');
Insert Into @Temp Values('closing in 5days');
Insert Into @Temp Values('closing in5 days');
Insert Into @Temp Values('closing in 15 days');
Insert Into @Temp Values('closing in3 days');
Insert Into @Temp Values('closing in 7 days');

Select	*, dbo.GetNumbers(Data)
From	@Temp 
Where	dbo.GetNumbers(Data) like '[1-5]'

[code]

Note that the code above appears to return just the rows you want.

-George
Microsoft SQL Server MVP
[url=http://blogs.lessthandot.com/index.php/All/?disp=authdir&author=10]My Blogs[/url]
[url=http://sqlcop.lessthandot.com]SQLCop[/url]
[url=http://www.twitter.com/gmmastros]twitter[/url]
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[burakcan]

Many thanks gmmastros!
It works . Appreciated.

 

[Andrzejek]

It is a custom on TT to award a star for helpful posts.
Use "[blue]Great post[/blue]" link to do so.
It also helps others to see which post(s) were helpful.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.