Registering Error: basically

Showing posts with label basically. Show all posts

Friday, March 30, 2012

Relatively new to SQL ...

How would I do something like this in a stored procedure'
Basically I want to declare ProductPrice as a sub routine type of thing..
Select * from products where {ProductPrice}
{ProductPrice}
(ProductID like "%Car%" or ProductID != NULL)
Help...
This is a simple example, the others I have require this kind of
functionality.
Thanks.If I understand what you're trying to do correctly, you do this:
CREATE PROC ProductPrice
AS
SELECT * from products WHERE ProductID LIKE "%Car%" OR ProductID IS NOT
NULL)
GO
You'd normally want to pass a parameter though instead of hard-coding a
search term like "%car%". Also, the where clause you gave isn't correct...I
think you might have meant "= null" instead of not equals.
"AshVsAOD" wrote:
> How would I do something like this in a stored procedure'
> Basically I want to declare ProductPrice as a sub routine type of thing..
> Select * from products where {ProductPrice}
> {ProductPrice}
> (ProductID like "%Car%" or ProductID != NULL)
>
> Help...
> This is a simple example, the others I have require this kind of
> functionality.
> Thanks.
>
>|||Yeah, I understand that. What I am after is. Can SQL use sub-procedures
like in my original piece of code?
> > Select * from products where {ProductPrice}
> >
> > {ProductPrice}
> > (ProductID like "%Car%" or ProductID != NULL)
Thanks for the help anyway.
"Mike Asher" <MikeAsher@.discussions.microsoft.com> wrote in message
news:FD3207AA-F1D0-460A-8979-E37E611A08B3@.microsoft.com...
> If I understand what you're trying to do correctly, you do this:
> CREATE PROC ProductPrice
> AS
> SELECT * from products WHERE ProductID LIKE "%Car%" OR ProductID IS NOT
> NULL)
> GO
> You'd normally want to pass a parameter though instead of hard-coding a
> search term like "%car%". Also, the where clause you gave isn't
correct...I
> think you might have meant "= null" instead of not equals.
>
> "AshVsAOD" wrote:
> > How would I do something like this in a stored procedure'
> >
> > Basically I want to declare ProductPrice as a sub routine type of
thing..
> >
> > Select * from products where {ProductPrice}
> >
> > {ProductPrice}
> > (ProductID like "%Car%" or ProductID != NULL)
> >
> >
> > Help...
> >
> > This is a simple example, the others I have require this kind of
> > functionality.
> >
> > Thanks.
> >
> >
> >|||AshVsAOD wrote:
> How would I do something like this in a stored procedure'
> Basically I want to declare ProductPrice as a sub routine type of
> thing..
> Select * from products where {ProductPrice}
> {ProductPrice}
> (ProductID like "%Car%" or ProductID != NULL)
>
> Help...
> This is a simple example, the others I have require this kind of
> functionality.
> Thanks.
Firstly, you cannot compare to NULL in that method. Nothing is equal to
NULL, not even NULL. If you want to compare the value (or lack thereof)
of a column to NULL you have to use "Where ColName IS NULL" or "Where
ColName IS NOT NULL". As an exercise, run the following code and examine
the results:
create table #NullTest (col1 nvarchar(10))
go
Insert Into #NullTest Values (NULL)
Insert Into #NullTest Values (N'')
Insert Into #NullTest Values (N'ABC')
go
Select col1 from #NullTest Where col1 != NULL
Select col1 from #NullTest Where col1 = NULL
Select col1 from #NullTest Where col1 IS NOT NULL
Select col1 from #NullTest Where col1 IS NULL
Select col1 from #NullTest Where col1 = N'ABC'
Drop Table #NullTest
Secondly, your query if changed to IS NOT NULL, will return all rows
that do not have a NULL ProductID.
"ProductID like "%Car%" or ProductID IS NOT NULL"
This means give me all products that have "Car" somewhere in the name
_and_ all products where the product id is not null.
You should also avoid using SELECT * syntax for result sets.
I'm not sure exactly what your criteria for the procedure is. Do you
want to pass a ProductID to the procedure and have the procedure return
the rows that match? If so...
Create Proc dbo.GetProducts
@.ProductID nvarchar(100)
as
Begin
Select
Col1,
Col2,
Col3
From dbo.Products
Where ProductID LIKE @.ProductID
End
Go
If you only need to do equality comparisons, then you can change the
"LIKE" to an "=".
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Ok,
So my example was poorly constructed.
I know not to use select *, I also know my query was rubbish.
What I don't know is, can I use sub routine type code in SQL.
For example Select blah from products where {productprice}
{productprice}
whatever statement here...
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
> AshVsAOD wrote:
> > How would I do something like this in a stored procedure'
> >
> > Basically I want to declare ProductPrice as a sub routine type of
> > thing..
> >
> > Select * from products where {ProductPrice}
> >
> > {ProductPrice}
> > (ProductID like "%Car%" or ProductID != NULL)
> >
> >
> > Help...
> >
> > This is a simple example, the others I have require this kind of
> > functionality.
> >
> > Thanks.
> Firstly, you cannot compare to NULL in that method. Nothing is equal to
> NULL, not even NULL. If you want to compare the value (or lack thereof)
> of a column to NULL you have to use "Where ColName IS NULL" or "Where
> ColName IS NOT NULL". As an exercise, run the following code and examine
> the results:
> create table #NullTest (col1 nvarchar(10))
> go
> Insert Into #NullTest Values (NULL)
> Insert Into #NullTest Values (N'')
> Insert Into #NullTest Values (N'ABC')
> go
> Select col1 from #NullTest Where col1 != NULL
> Select col1 from #NullTest Where col1 = NULL
> Select col1 from #NullTest Where col1 IS NOT NULL
> Select col1 from #NullTest Where col1 IS NULL
> Select col1 from #NullTest Where col1 = N'ABC'
> Drop Table #NullTest
>
> Secondly, your query if changed to IS NOT NULL, will return all rows
> that do not have a NULL ProductID.
> "ProductID like "%Car%" or ProductID IS NOT NULL"
> This means give me all products that have "Car" somewhere in the name
> _and_ all products where the product id is not null.
> You should also avoid using SELECT * syntax for result sets.
> I'm not sure exactly what your criteria for the procedure is. Do you
> want to pass a ProductID to the procedure and have the procedure return
> the rows that match? If so...
>
> Create Proc dbo.GetProducts
> @.ProductID nvarchar(100)
> as
> Begin
> Select
> Col1,
> Col2,
> Col3
> From dbo.Products
> Where ProductID LIKE @.ProductID
> End
> Go
> If you only need to do equality comparisons, then you can change the
> "LIKE" to an "=".
>
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||> Yeah, I understand that. What I am after is. Can SQL use sub-procedures
> like in my original piece of code?
Yes you can; one stored proc can call another, or itself recursively.
Return values other than result sets can be passed back to the caller via
output parameters. If you want to use in-line "function-type" syntax,
though, you'll need to code the procedure as a user-defined function.|||This is a multi-part message in MIME format.
--050701000406080309060203
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
As Mike Asher posted, you can call stored procs from other stored procs
but that's not what you're talking about here in your example (from what
I can tell). You're talking about nested sub-queries, which basically
are categorized into scalar subqueries (that return a single column,
single row) and correlated subqueries (which are dependent on one or
more values from the outer query). I'm guessing you want to do a
correlated subquery like:
select blah from products p
where exists (select * from orders o where o.productID = p.productID)
or
select blah from products p
where productID in (select productID from orders o where customer = 'ACME Corp')
Something like that. Of course this won't allow code reuse and there
are better ways to write these queries (for example quite often
correlated subqueries can be rewritten with joins instead of the
subquery and in many cases perform better with the joins) but in my
understanding that's basically what you were asking. You cannot call a
stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
statement but you can include subqueries against views and/or tables.
Additionally, the "tables" in the FROM clause can alternately be tables,
views or derived tables (basically a SELECT statement wrapped in
parentheses and given a table alias).
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AshVsAOD wrote:
>Ok,
>So my example was poorly constructed.
>I know not to use select *, I also know my query was rubbish.
>What I don't know is, can I use sub routine type code in SQL.
>For example Select blah from products where {productprice}
>{productprice}
>whatever statement here...
>
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
>
>>AshVsAOD wrote:
>>
>>How would I do something like this in a stored procedure'
>>Basically I want to declare ProductPrice as a sub routine type of
>>thing..
>>Select * from products where {ProductPrice}
>>{ProductPrice}
>>(ProductID like "%Car%" or ProductID != NULL)
>>
>>Help...
>>This is a simple example, the others I have require this kind of
>>functionality.
>>Thanks.
>>
>>Firstly, you cannot compare to NULL in that method. Nothing is equal to
>>NULL, not even NULL. If you want to compare the value (or lack thereof)
>>of a column to NULL you have to use "Where ColName IS NULL" or "Where
>>ColName IS NOT NULL". As an exercise, run the following code and examine
>>the results:
>>create table #NullTest (col1 nvarchar(10))
>>go
>>Insert Into #NullTest Values (NULL)
>>Insert Into #NullTest Values (N'')
>>Insert Into #NullTest Values (N'ABC')
>>go
>>Select col1 from #NullTest Where col1 != NULL
>>Select col1 from #NullTest Where col1 = NULL
>>Select col1 from #NullTest Where col1 IS NOT NULL
>>Select col1 from #NullTest Where col1 IS NULL
>>Select col1 from #NullTest Where col1 = N'ABC'
>>Drop Table #NullTest
>>
>>Secondly, your query if changed to IS NOT NULL, will return all rows
>>that do not have a NULL ProductID.
>>"ProductID like "%Car%" or ProductID IS NOT NULL"
>>This means give me all products that have "Car" somewhere in the name
>>_and_ all products where the product id is not null.
>>You should also avoid using SELECT * syntax for result sets.
>>I'm not sure exactly what your criteria for the procedure is. Do you
>>want to pass a ProductID to the procedure and have the procedure return
>>the rows that match? If so...
>>
>>Create Proc dbo.GetProducts
>>@.ProductID nvarchar(100)
>>as
>>Begin
>> Select
>> Col1,
>> Col2,
>> Col3
>> From dbo.Products
>> Where ProductID LIKE @.ProductID
>>End
>>Go
>>If you only need to do equality comparisons, then you can change the
>>"LIKE" to an "=".
>>
>>
>>--
>>David Gugick
>>Quest Software
>>www.imceda.com
>>www.quest.com
>>
>
>
--050701000406080309060203
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>As Mike Asher posted, you can call stored procs from other stored
procs but that's not what you're talking about here in your example
(from what I can tell). You're talking about nested sub-queries, which
basically are categorized into scalar subqueries (that return a single
column, single row) and correlated subqueries (which are dependent on
one or more values from the outer query). I'm guessing you want to do
a correlated subquery like: 
</tt>
<blockquote><tt>select blah from products p</tt> 
<tt>where exists (select * from orders o where o.productID =p.productID)</tt> 
</blockquote>
<tt>or 
</tt>
<blockquote><tt>select blah from products p</tt> 
<tt>where productID in (select productID from orders o where customer
= 'ACME Corp')</tt> 
</blockquote>
<tt> 
Something like that. Of course this won't allow code reuse and there
are better ways to write these queries (for example quite often
correlated subqueries can be rewritten with joins instead of the
subquery and in many cases perform better with the joins) but in my
understanding that's basically what you were asking. You cannot call a
stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
statement but you can include subqueries against views and/or tables.
Additionally, the "tables" in the FROM clause can alternately be
tables, views or derived tables (basically a SELECT statement wrapped
in parentheses and given a table alias). 
 
HTH 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
AshVsAOD wrote:
<blockquote cite="mide79syxudFHA.584@.TK2MSFTNGP15.phx.gbl" type="cite">
<pre wrap="">Ok,
So my example was poorly constructed.
I know not to use select *, I also know my query was rubbish.
What I don't know is, can I use sub routine type code in SQL.
For example Select blah from products where {productprice}
{productprice}
whatever statement here...
"David Gugick" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:david.gugick-nospam@.quest.com"><david.gugick-nospam@.quest.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl">news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">AshVsAOD wrote:
</pre>
<blockquote type="cite">
<pre wrap="">How would I do something like this in a stored procedure'
Basically I want to declare ProductPrice as a sub routine type of
thing..
Select * from products where {ProductPrice}
{ProductPrice}
(ProductID like "%Car%" or ProductID != NULL)
Help...
This is a simple example, the others I have require this kind of
functionality.
Thanks.
</pre>
</blockquote>
<pre wrap="">Firstly, you cannot compare to NULL in that method. Nothing is equal to
NULL, not even NULL. If you want to compare the value (or lack thereof)
of a column to NULL you have to use "Where ColName IS NULL" or "Where
ColName IS NOT NULL". As an exercise, run the following code and examine
the results:
create table #NullTest (col1 nvarchar(10))
go
Insert Into #NullTest Values (NULL)
Insert Into #NullTest Values (N'')
Insert Into #NullTest Values (N'ABC')
go
Select col1 from #NullTest Where col1 != NULL
Select col1 from #NullTest Where col1 = NULL
Select col1 from #NullTest Where col1 IS NOT NULL
Select col1 from #NullTest Where col1 IS NULL
Select col1 from #NullTest Where col1 = N'ABC'
Drop Table #NullTest
Secondly, your query if changed to IS NOT NULL, will return all rows
that do not have a NULL ProductID.
"ProductID like "%Car%" or ProductID IS NOT NULL"
This means give me all products that have "Car" somewhere in the name
_and_ all products where the product id is not null.
You should also avoid using SELECT * syntax for result sets.
I'm not sure exactly what your criteria for the procedure is. Do you
want to pass a ProductID to the procedure and have the procedure return
the rows that match? If so...
Create Proc dbo.GetProducts
@.ProductID nvarchar(100)
as
Begin
Select
Col1,
Col2,
Col3
From dbo.Products
Where ProductID LIKE @.ProductID
End
Go
If you only need to do equality comparisons, then you can change the
"LIKE" to an "=".
David Gugick
Quest Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.quest.com</a>">http://www.quest.com">www.quest.com</a>
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--050701000406080309060203--|||Thanks,
and sorry.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:OB6ktMvdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> As Mike Asher posted, you can call stored procs from other stored procs
> but that's not what you're talking about here in your example (from what
> I can tell). You're talking about nested sub-queries, which basically
> are categorized into scalar subqueries (that return a single column,
> single row) and correlated subqueries (which are dependent on one or
> more values from the outer query). I'm guessing you want to do a
> correlated subquery like:
> select blah from products p
> where exists (select * from orders o where o.productID = p.productID)
> or
> select blah from products p
> where productID in (select productID from orders o where customer => 'ACME Corp')
>
> Something like that. Of course this won't allow code reuse and there
> are better ways to write these queries (for example quite often
> correlated subqueries can be rewritten with joins instead of the
> subquery and in many cases perform better with the joins) but in my
> understanding that's basically what you were asking. You cannot call a
> stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
> statement but you can include subqueries against views and/or tables.
> Additionally, the "tables" in the FROM clause can alternately be tables,
> views or derived tables (basically a SELECT statement wrapped in
> parentheses and given a table alias).
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
http://www.mallesons.com
>
> AshVsAOD wrote:
> >Ok,
> >
> >So my example was poorly constructed.
> >
> >I know not to use select *, I also know my query was rubbish.
> >
> >What I don't know is, can I use sub routine type code in SQL.
> >
> >For example Select blah from products where {productprice}
> >
> >{productprice}
> >whatever statement here...
> >
> >
> >
> >"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> >news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
> >
> >
> >>AshVsAOD wrote:
> >>
> >>
> >>How would I do something like this in a stored procedure'
> >>
> >>Basically I want to declare ProductPrice as a sub routine type of
> >>thing..
> >>
> >>Select * from products where {ProductPrice}
> >>
> >>{ProductPrice}
> >>(ProductID like "%Car%" or ProductID != NULL)
> >>
> >>
> >>Help...
> >>
> >>This is a simple example, the others I have require this kind of
> >>functionality.
> >>
> >>Thanks.
> >>
> >>
> >>Firstly, you cannot compare to NULL in that method. Nothing is equal to
> >>NULL, not even NULL. If you want to compare the value (or lack thereof)
> >>of a column to NULL you have to use "Where ColName IS NULL" or "Where
> >>ColName IS NOT NULL". As an exercise, run the following code and examine
> >>the results:
> >>
> >>create table #NullTest (col1 nvarchar(10))
> >>go
> >>
> >>Insert Into #NullTest Values (NULL)
> >>Insert Into #NullTest Values (N'')
> >>Insert Into #NullTest Values (N'ABC')
> >>go
> >>
> >>Select col1 from #NullTest Where col1 != NULL
> >>Select col1 from #NullTest Where col1 = NULL
> >>Select col1 from #NullTest Where col1 IS NOT NULL
> >>Select col1 from #NullTest Where col1 IS NULL
> >>Select col1 from #NullTest Where col1 = N'ABC'
> >>
> >>Drop Table #NullTest
> >>
> >>
> >>Secondly, your query if changed to IS NOT NULL, will return all rows
> >>that do not have a NULL ProductID.
> >>
> >>"ProductID like "%Car%" or ProductID IS NOT NULL"
> >>
> >>This means give me all products that have "Car" somewhere in the name
> >>_and_ all products where the product id is not null.
> >>
> >>You should also avoid using SELECT * syntax for result sets.
> >>
> >>I'm not sure exactly what your criteria for the procedure is. Do you
> >>want to pass a ProductID to the procedure and have the procedure return
> >>the rows that match? If so...
> >>
> >>
> >>Create Proc dbo.GetProducts
> >>@.ProductID nvarchar(100)
> >>as
> >>Begin
> >> Select
> >> Col1,
> >> Col2,
> >> Col3
> >> From dbo.Products
> >> Where ProductID LIKE @.ProductID
> >>End
> >>Go
> >>
> >>If you only need to do equality comparisons, then you can change the
> >>"LIKE" to an "=".
> >>
> >>
> >>
> >>
> >>--
> >>David Gugick
> >>Quest Software
> >>www.imceda.com
> >>www.quest.com
> >>
> >>
> >>
> >
> >
> >
> >
>|||Cheers!
"Mike Asher" <MikeAsher@.discussions.microsoft.com> wrote in message
news:316F437C-80F2-4127-846D-2A87C73960A2@.microsoft.com...
> > Yeah, I understand that. What I am after is. Can SQL use
sub-procedures
> > like in my original piece of code?
> Yes you can; one stored proc can call another, or itself recursively.
> Return values other than result sets can be passed back to the caller via
> output parameters. If you want to use in-line "function-type" syntax,
> though, you'll need to code the procedure as a user-defined function.

Relatively new to SQL ...

Wednesday, March 28, 2012

Relationship to UserId

Hi there everyone, this is my first post so go easy on me :)

Basically I am trying to get my database to copy the value in the UserId (unique identifier field) from the aspnet_Users table to a foreign key UserId in a table called userclassset. I have made this field the same datatype and created a relationship between the two. Unfortunately, when I add a user using the ASP.Net configuration tool it does not automatically copy this value into my own custom table. I have noticed it is however automatically copied into the aspnet_Membership table. Any pointers on how to solve this would be great!

Thanks :)

ascension:
?I have noticed it is however automatically copied into the aspnet_Membership table. Any pointers on how to solve this would be great!

This is because when you?use?.NET2.0?built-in?function?to create user,?the?aspnet_Users_CreateUser?stored?procedure?is?called.?This?stored?procedure?will?not?only?insert?a?row?in?aspnet_users?table,?but?also?do?some?operations?on?aspnet_Memebership?table,?you?can?use?sp_helptext?to?view?the?definition:

sp_helptext aspnet_Users_CreateUser

So if you want to insert some row into your own custom table, you can either: alter the aspnet_Users_CreateUser to add your own statements (preferred); orcreate a?insert trigger?on?the?aspnet_users?table|||Brilliant, worked perfectly. Thanks :)

Friday, March 9, 2012

Regular Expressions in SQL 2005 using C#

Hi all,

i wrote a little function that is basically supposed to give me a match of a regular expression in C#. i tested it out in VS05 and it seems to work fine. It hits the fan when i try it on SQL Query analyzer once deployed. Anyone have any idea why?

here is the C# class that I deploy to a db using VS05.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

public partial class RegExTest

{

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]

public static string RegExMatch(string pattern, string matchString)

{

Regex r1 = new Regex(pattern);

if (r1.Match(matchString.TrimEnd(null)).Success)

{

Regex testReg = new Regex(pattern);

Match regMatch = testReg.Match(matchString);

string runnumber = regMatch.ToString().TrimEnd('_');

runnumber = runnumber.TrimStart('_');

return runnumber;

}

else

{

return null;

}

};

Basically i have a teststring that looks like abc_xxx_nnndef. xxx and nn are both numbers, and im using the pattern _\\d{3}_ to test it out in SQL (i need the numbers between '_' and '_'). But it never even proceeds past the if statement in SQL i think. here is the basic SQL that i use to test the above function:

use northwind

declare @.teststring varchar(50),

@.pattern varchar(10),

@.out varchar(50)

set @.teststring = 'dw_012_34.ext'

set @.pattern = '_\\d{3}_'

set @.out = dbo.RegExMatch(@.pattern, @.teststring )

select @.teststring

select @.pattern

select @.out

Anyone have any suggestions as to why it works in VS 05 when I use it in a C# program, but fails on the SQL version?

Hi,

without escaping the string this, worked for me, the pattern therefore was _\d{3}_ not this with the double backslash.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Actually, I had made a silly mistake testing my code. The above code works just fine. Sorry for the inconvenience!

|||

i am getting the error while calling

dbo.RegExMatch(@.pattern, @.teststring )

this line in stored procedure,

i have already created the assembly.

still i am getting the error!

|||Which error ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

the scene is this:

after creating an assembly name Reg_ex,

i need to call the method regmatch which is in Reg_ex, in class regtest.

when i say"

CREATE PROCEDURE RegExMatch

AS external name Reg_Ex.RegTest.RegExMatch

error is:

Msg 6505, Level 16, State 1, Procedure RegExMatch, Line 1

Could not find Type 'RegTest' in assembly 'Reg_Ex'.

Regular Expressions in SQL 2005 using C#

Hi all,

here is the C# class that I deploy to a db using VS05.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

public partial class RegExTest

{

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]

public static string RegExMatch(string pattern, string matchString)

{

Regex r1 = new Regex(pattern);

if (r1.Match(matchString.TrimEnd(null)).Success)

{

Regex testReg = new Regex(pattern);

Match regMatch = testReg.Match(matchString);

string runnumber = regMatch.ToString().TrimEnd('_');

runnumber = runnumber.TrimStart('_');

return runnumber;

}

else

{

return null;

}

};

use northwind

declare @.teststring varchar(50),

@.pattern varchar(10),

@.out varchar(50)

set @.teststring = 'dw_012_34.ext'

set @.pattern = '_\\d{3}_'

set @.out = dbo.RegExMatch(@.pattern, @.teststring )

select @.teststring

select @.pattern

select @.out

Anyone have any suggestions as to why it works in VS 05 when I use it in a C# program, but fails on the SQL version?

Hi,

without escaping the string this, worked for me, the pattern therefore was _\d{3}_ not this with the double backslash.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Actually, I had made a silly mistake testing my code. The above code works just fine. Sorry for the inconvenience!

|||

i am getting the error while calling

dbo.RegExMatch(@.pattern, @.teststring )

this line in stored procedure,

i have already created the assembly.

still i am getting the error!

|||Which error ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

the scene is this:

after creating an assembly name Reg_ex,

i need to call the method regmatch which is in Reg_ex, in class regtest.

when i say"

CREATE PROCEDURE RegExMatch

AS external name Reg_Ex.RegTest.RegExMatch

error is:

Msg 6505, Level 16, State 1, Procedure RegExMatch, Line 1

Could not find Type 'RegTest' in assembly 'Reg_Ex'.

Friday, March 30, 2012

Relatively new to SQL ...

Relatively new to SQL ...

Wednesday, March 28, 2012

Relationship to UserId

Friday, March 9, 2012

Regular Expressions in SQL 2005 using C#

Regular Expressions in SQL 2005 using C#

Registering Error

Blog Archive

About Me