unlike regex, sql matches only support wild card or single char match, no
real catches. [] match is a single char match.
you best bet is to write a user defined function that does the match.
actually if you are allowed to write clr function, its easy to a real
regex match. something like:
[Microsoft.SqlServer.Server.SqlFunction()]
public static bool RegExMatch(string input, string pattern)
{
return new Regex(pattern).IsMatch(input);
}
-- bruce (sqlwork.com)
Thanks Bruce, my goal is to return only max value that has only numeric
characters, so I can increment up by 1, I thought [^A-Z^a-z] acted as an
overall filter not just on the last character, in Access db I could use #
instead of _ as a wildcard to return only numeric values but can't find
that in SQL, any thoughts as to how to write my pattern match? I parse
the string after this to get just the 4 characters after the 08- to
increment up.
John
bruce barker said:
first its not a regex expression, its a sql pattern match expression and
has
a different format.
second it depend on want your column values look like. your match just
says
to match any string at least 8 characters long, starting with "08-" and
not
ending in a letter. the following will match
08-abcd1
08-abcdegf.
08-aj.><>()*&^%$#@!
-- bruce (sqlwork.com)
:
this does eleminate strings with letters from the results, any ideas
myPattern = "08-____%[^A-Z^a-z]"
Dim h As Integer = 0
Dim i As Integer = InStr(myPattern, "%")
Dim job = (From j In dbJob.JobRecs _
Where Linq.SqlClient.SqlMethods.Like(j.JobNumber, myPattern) _
And j.OwnerID = hfOwnerID.Value _
Select j.JobNumber).Max.Substring(h, i).ToString
