regular expression invalid object name

[TonyE]

Hello;
I am using SSIS on SQL Server 2008. I am trying to use a reg
expression to extract only the numeric characters out of a varchar
field. It looks like this:

select regexmatch(PROPERTY_LOCATION, [0-9])
from P_PROP;

I get the message invalid object name regexmatch.

I tried adding 'dbo' to the beginning as well with the same result.

Any ideas as to what I am doing wrong?

[Bob Barrows]

Post by TonyE
Hello;
I am using SSIS on SQL Server 2008. I am trying to use a reg
expression to extract only the numeric characters out of a varchar
select regexmatch(PROPERTY_LOCATION, [0-9])
from P_PROP;
I get the message invalid object name regexmatch.
I tried adding 'dbo' to the beginning as well with the same result.
Any ideas as to what I am doing wrong?

What gave you the idea to try this code? I've done a search for "regexmatch"
and got no hits that related to SSIS. Where is SSIS supposed to find this
function? It's certainly not a builtin SQL Server function. Is it a custom
UDF that you've written? If so, have you tried running this query on the
server where your UDF is installed?

[Erland Sommarskog]

Post by TonyE
I am using SSIS on SQL Server 2008. I am trying to use a reg
expression to extract only the numeric characters out of a varchar
select regexmatch(PROPERTY_LOCATION, [0-9])
from P_PROP;
I get the message invalid object name regexmatch.
I tried adding 'dbo' to the beginning as well with the same result.
Any ideas as to what I am doing wrong?

There is no built-in function in SQL Server with that name. So maybe your
mistake is that you believe there is, confusing SQL Server with a different
product?

Or you have a user-written function for this purpose somewhere, but you have
not spelt it correctly. Or it is in a different schema or a different
database.

I'm sorry, but without knowing why you expect this to work, we cannot really
be more helpful than this.

[TonyE]

Post by TonyE
Hello;
I am using SSIS on SQL Server 2008. I am trying to use a reg
expression to extract only the numeric characters out of a varchar
select regexmatch(PROPERTY_LOCATION, [0-9])
from P_PROP;
I get the message invalid object name regexmatch.
I tried adding 'dbo' to the beginning as well with the same result.
Any ideas as to what I am doing wrong?

Hello
I am sorry for the misguided inquiry. I am at work and typed it up in
a hurry--I am using SQL Server Management Studio
and I am new to all this.
I tried using regexmatch because I researched the web and found it.
One web site that showed this to me is http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
I want to use a regular expresion because its a lot more efficient.
I know this is simple, all I want to do is pull out any characters
that have a value of 0-9 in a varchar field.

[Erland Sommarskog]

Post by TonyE
I am sorry for the misguided inquiry. I am at work and typed it up in
a hurry--I am using SQL Server Management Studio
and I am new to all this.
I tried using regexmatch because I researched the web and found it.
One web site that showed this to me is
http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

That article shows how you can implement regular expression searches in
SQL Server by writing a function in C#. That is, you cold take the code
in that article and install it in your database. You would need to read
the article in full to learn all steps.

Post by TonyE
I want to use a regular expresion because its a lot more efficient.
I know this is simple, all I want to do is pull out any characters
that have a value of 0-9 in a varchar field.

For such a simple pattern, there exists a built-in function in SQL Server:

SELECT *
FROM tbl
WHERE patindex('%[0-9]%', PROPERTY_LOCATION) > 0

The search patterns in SQL are not regular expressions. % works as wild card
for zero or more occurrances of any character.