Relate a Contact to Customer

I have a contact table and a customer table. The two tables will contain columns like

First name

Last Name,

Date of Birth

Post Code,

House Number

Street Name

etc.

I would like to find the different combinations in which I can relate the customer and contact data.

Like its is possible that the first name and last name are same but date of birth is different. This indicates that the contact and customer is the same. Now I do not know these combinations and I would like to have this set generated for me.

From Integration Service (Sql Server 2005) I get the data and I would like to know the patterns in which data will differ. Is there any way of achieving this?

I am very new to Data Mining and would like to have some direction as to how to progress with this.

The fuzzy match functionality in Integration Services is a better solution for this problem.

Data Mining requires a training set that already has the patterns you wish to discover - once you train a mining model with that data set, you can then apply those patterns to new data and predict missing information (potentially, depending on the type of algorithm you use to build the model).

Reinitializing SqlCommand object

Hi All,
Can the same connection object and the command object be reinitialised and
re-used through-out the project? Is the code below permissible?
Dim DBConnection As SqlConnection
Dim oCmd As SqlCommand
Dim sStmt As String
DBConnection.Open()
sStmt = "select count(*)......"
oCmd = New SqlCommand(sStmt, DBConnection)
oCmd.CommandText = CommandType.Text
ocmd.ExecuteScalar()
.....
...
...
sStmt = "insert ..."
oCmd = New SqlCommand(sStmt, DBConnection)
oCmd.CommandText = CommandType.Text
DBConnection.Open()
ocmd.ExecuteNonQuery()
.....
...
...
sStmt = "update......"
oCmd = New SqlCommand(sStmt, DBConnection)
oCmd.CommandText = CommandType.Text
DBConnection.Open()
ocmd.ExecuteNonQuery()
.....
...
...
oCmd.Connection.Close()
Would previous instances of SqlCommand object be garbage collected
automatically?
kdkd,
This is a .NET question, not SQL Server, but I'll field it.

> Can the same connection object and the command object be
> reinitialised and re-used through-out the project?
First off, in your code, you are not reusing the *object* you are reusing
the *reference* (the variable name) . There is a difference.
Second, you say "through-out the project" which to me implies global usage.
That's a bad, bad, bad practice. Declare, instantiate, Open, Use, and then
Close your connection only when you need it. No more.
If you have a series of commands to execute one after another, the pattern I
would recommend is this:
Dim myCommand as SqlCommand
Dim myConnection as New SqlConnection(ConnectionString)
myConnection.Open()
myCommand = New SqlCommand(sql,myConnection)
myCommand.ExecuteNonQuery
myCommand = New SqlCommand(sql,myConnection)
myCommand.ExecuteNonQuery
...
myConnection.Close()

> Would previous instances of SqlCommand object be
> garbage collected automatically?
Garbage collection in .NET is not immediate. It's "when it gets to it." But
yes, it will be collected. "myConnection = nothing" is uneeded and not
recommended.
Even though a connection is automatically closed when GC'ed, I still
recommend an explicit "myConnection.Close()." This will free up the ADO.NET
connection pool.
And one final note, consider looking at MSDN naming guidelines. In .NET, we
do not use "sStmt" or "oCmd".
--Alex Papadimoulis
"kd" wrote:

> Hi All,
> Can the same connection object and the command object be reinitialised and
> re-used through-out the project? Is the code below permissible?
> Dim DBConnection As SqlConnection
> Dim oCmd As SqlCommand
> Dim sStmt As String
> DBConnection.Open()
> sStmt = "select count(*)......"
> oCmd = New SqlCommand(sStmt, DBConnection)
> oCmd.CommandText = CommandType.Text
> ocmd.ExecuteScalar()
> ......
> ....
> ....
> sStmt = "insert ..."
> oCmd = New SqlCommand(sStmt, DBConnection)
> oCmd.CommandText = CommandType.Text
> DBConnection.Open()
> ocmd.ExecuteNonQuery()
> ......
> ....
> ....
> sStmt = "update......"
> oCmd = New SqlCommand(sStmt, DBConnection)
> oCmd.CommandText = CommandType.Text
> DBConnection.Open()
> ocmd.ExecuteNonQuery()
> ......
> ....
> ....
> oCmd.Connection.Close()
> Would previous instances of SqlCommand object be garbage collected
> automatically?
> kd|||Hi Alex,
Thanks for the guidelines.
Regards,
kd
"Alex Papadimoulis" wrote:
> kd,
> This is a .NET question, not SQL Server, but I'll field it.
>
> First off, in your code, you are not reusing the *object* you are reusing
> the *reference* (the variable name) . There is a difference.
> Second, you say "through-out the project" which to me implies global usage
.
> That's a bad, bad, bad practice. Declare, instantiate, Open, Use, and then
> Close your connection only when you need it. No more.
> If you have a series of commands to execute one after another, the pattern
I
> would recommend is this:
> Dim myCommand as SqlCommand
> Dim myConnection as New SqlConnection(ConnectionString)
> myConnection.Open()
> myCommand = New SqlCommand(sql,myConnection)
> myCommand.ExecuteNonQuery
> myCommand = New SqlCommand(sql,myConnection)
> myCommand.ExecuteNonQuery
> ...
> myConnection.Close()
>
> Garbage collection in .NET is not immediate. It's "when it gets to it." Bu
t
> yes, it will be collected. "myConnection = nothing" is uneeded and not
> recommended.
> Even though a connection is automatically closed when GC'ed, I still
> recommend an explicit "myConnection.Close()." This will free up the ADO.NE
T
> connection pool.
> And one final note, consider looking at MSDN naming guidelines. In .NET, w
e
> do not use "sStmt" or "oCmd".
> --Alex Papadimoulis
>
> "kd" wrote:
>|||Hi,
Would you be able to provide links for naming guidelines in .NET?
Thanks
kd
"Alex Papadimoulis" wrote:
> kd,
> This is a .NET question, not SQL Server, but I'll field it.
>
> First off, in your code, you are not reusing the *object* you are reusing
> the *reference* (the variable name) . There is a difference.
> Second, you say "through-out the project" which to me implies global usage
.
> That's a bad, bad, bad practice. Declare, instantiate, Open, Use, and then
> Close your connection only when you need it. No more.
> If you have a series of commands to execute one after another, the pattern
I
> would recommend is this:
> Dim myCommand as SqlCommand
> Dim myConnection as New SqlConnection(ConnectionString)
> myConnection.Open()
> myCommand = New SqlCommand(sql,myConnection)
> myCommand.ExecuteNonQuery
> myCommand = New SqlCommand(sql,myConnection)
> myCommand.ExecuteNonQuery
> ...
> myConnection.Close()
>
> Garbage collection in .NET is not immediate. It's "when it gets to it." Bu
t
> yes, it will be collected. "myConnection = nothing" is uneeded and not
> recommended.
> Even though a connection is automatically closed when GC'ed, I still
> recommend an explicit "myConnection.Close()." This will free up the ADO.NE
T
> connection pool.
> And one final note, consider looking at MSDN naming guidelines. In .NET, w
e
> do not use "sStmt" or "oCmd".
> --Alex Papadimoulis
>
> "kd" wrote:
>|||kd,
[url]http://msdn.microsoft.com/library/en-us/cpgenref/html/cpconnamingguidelines.asp[/u
rl]
-- Alex Papadimoulis
"kd" wrote:
> Hi,
> Would you be able to provide links for naming guidelines in .NET?
> Thanks
> kd
> "Alex Papadimoulis" wrote:
>

regular expressions in transact sql

hi guys,

i need some help tp write code in order to search the string ( regular expressions) in t- sql.

e.g.

when a user enters [A-Z] it means any alphabet from 'A' to 'Z'...

similarly [0-9] means any digit.

the problem is: when user enters [0-6] and the string received contains digit 5 it should return true but if it contains 7 it should return false.

so how do i read the [A-Z] as a range of characters in t-sql?

hi,

In sql server it is not exactly regular expression, it is called wild card pattern. in other words it is simplified reqular expression,

as of now SQL Server Like operator only work with following operators

% - Zero or any number of chars

_ - Single Char

[] - Single Char in given range

- Single Char not in given range

if you want to utilize the exact regular expression on your query then the best solution will be CLR Functions.(SQL Server 2005).

For fixed validation (only numbers & only alphabets) i achived the following function,

Create Function dbo.IsMatching(@.Value as varchar(1000), @.Pattern as varchar(100))
returns bit as
Begin
Declare @.Len as int;
Declare @.SearchPattern as varchar(8000);
Declare @.Result as Int;

Select @.Len = Len(@.Value);

While @.Len>0
Begin
Select @.SearchPattern = Isnull(@.SearchPattern,'') + @.Pattern;
Select @.Len = @.Len -1;
End
Select @.Result = Case When @.Value Like @.SearchPattern Then 1 Else 0 End;
Return @.Result;
End

Go

select dbo.IsMatching('SQLServer','[A-Z]') as Result

Result : 1

select dbo.IsMatching('SQL Server','[A-Z]') as Result

Result : 0 --Space on String

select dbo.IsMatching('SQL Server','[A-Z ]') as Result

Result : 1 --Space added on Pattern

select dbo.IsMatching('12453','[1-5]') as Result

Result : 1

select dbo.IsMatching('12463','[1-5]') as Result

Result : 0

|||

thanks mani,

got the [A-Z] and [^a-z] concept.

my other requirements are to match zero or more characters and to match one or more characters.

e.g. T*he should match he, the, tthe, ttttthe.. etc.

and t+ho should match tho, thho, thhhhhhho, thhhhhhhhhhhhhho.. etc.

the above operators i have used in VC++,

do they work in t-sql too?

|||If you need regular expression and your platform is sqlserver 2005 you can use a CLR strored procedure. If you need help on this post a question on the .net framework inside sql server forum
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=86&SiteID=1|||

Yes.. if you want to find the repeated chars you can use the following condtions....

columnname not like '%aaa%'
and columnname not like '%bbb%'
and columnname not like '%ccc%'
and columnname not like '%ddd%'
and columnname not like '%eee%'
and columnname not like '%fff%'
and columnname not like '%ggg%'
and columnname not like '%hhh%'
and columnname not like '%iiii%'
and columnname not like '%jjj%'
and columnname not like '%kkk%'
and columnname not like '%lll%'
and columnname not like '%mmm%'
and columnname not like '%nnn%'
and columnname not like '%ooo%'
and columnname not like '%ppp%'
and columnname not like '%qqq%'
and columnname not like '%rrr%'
and columnname not like '%sss%'
and columnname not like '%ttt%'
and columnname not like '%uuu%'
and columnname not like '%vvv%'
and columnname not like '%www%'
and columnname not like '%xxx%'
and columnname not like '%yyy%'
and columnname not like '%zzz%'

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=996813&SiteID=1

If you want to utilize the exact regular expression as i said earlier you can go for CLR Functions..

regular expression

Hi all,
I m able to use regular expression in a Vbs script, but how can I use them
(if it's possible ) in reporting service code ?
I havent found any subjects like this in the my books (even in "MRS in
action" by Téo Lachev :) )
ThanksFirst, add a function to do your Regular expression in the report level
Code (Report --> Properties --> Code) . Here's an example:
----
Public Function RegExTest () as Object
Dim Str As String = "<expressiontext>the text the returned by
the regular expression</expressiontext>"
Dim expressiontext As String
Dim regexexpressiontext As System.Text.RegularExpressions.Regex
= New
System.Text.RegularExpressions.Regex("<expressiontext>(?<expressiontext>[^<]+)</expressiontext>",
System.Text.RegularExpressions.RegexOptions.IgnoreCase)
expressiontext =regexexpressiontext.Match(Str).Groups("expressiontext").Value
RegExTest = expressiontext
End Function
----
Now your text box will refer to the code in a fashion similar to this:
=Code.RegExTest
And low and behold, regular expressions in Reporting Services.
Andy Potter|||OK.
Thanks a lot !
"Potter" wrote:
> First, add a function to do your Regular expression in the report level
> Code (Report --> Properties --> Code) . Here's an example:
> ----
> Public Function RegExTest () as Object
> Dim Str As String = "<expressiontext>the text the returned by
> the regular expression</expressiontext>"
> Dim expressiontext As String
> Dim regexexpressiontext As System.Text.RegularExpressions.Regex
> = New
> System.Text.RegularExpressions.Regex("<expressiontext>(?<expressiontext>[^<]+)</expressiontext>",
> System.Text.RegularExpressions.RegexOptions.IgnoreCase)
> expressiontext => regexexpressiontext.Match(Str).Groups("expressiontext").Value
> RegExTest = expressiontext
> End Function
> ----
> Now your text box will refer to the code in a fashion similar to this:
> =Code.RegExTest
> And low and behold, regular expressions in Reporting Services.
> Andy Potter
>