Case statemant- separate forenames

Hi All

I need to separate the forenames within the staff table, at present it looks something like this:

John-Test
John - Test
John - Test
Richard
Bill Jones
John - Test MiddleName
John-Test Mid

I need to split the middlenames out and remove the spaces within the double barrelled names

John-Test
John-Test
John-Test
Richard
Bill
John-Test
John-Test

Thanks in advanced

Rich

hi...

there is a recent post solving a similar issue..have a look...

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

|||

It will do..

Create Table Names
(
FullName varchar(100)
)
Go

Insert Into Names values ('John-Test ')
Insert Into Names values ('John - Test')
Insert Into Names values ('John - Test')
Insert Into Names values ('Richard')
Insert Into Names values ('Bill Jones')
Insert Into Names values ('John - Test MiddleName')
Insert Into Names values ('John-Test Mid')

Go

--Simple / Direct Logic

Select
A Actual
,B + Substring(C,1,CharIndex(' ',C)) Result
From
(
Select
FullName A
,Replace(Substring(FullName,1,CharIndex('-',FullName)),' ','') B
,Ltrim(Substring(FullName,CharIndex('-',FullName)+1,len(FullName)-CharIndex('-',FullName))) + ' ' C
from
Names
) as Data

Go

--Creating Function
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetProperName]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetProperName]
Go

Create Function Dbo.GetProperName(@.Name as varchar(100)) Returns Varchar(100)
as
Begin
Declare @.B as Varchar(100);
Declare @.C as Varchar(100);
Declare @.Result as Varchar(100);

Select

@.B = Replace(Substring(@.Name,1,CharIndex('-',@.Name)),' ','')
,@.C = Ltrim(Substring(@.Name,CharIndex('-',@.Name)+1,len(@.Name)-CharIndex('-',@.Name))) + ' '

Select @.Result = @.B + Substring(@.C,1,CharIndex(' ',@.C))
return @.Result;
End

go
Select FullName, Dbo.GetProperName(fullname) from names
go
Drop Table Names

Case Sensitive?

Dear everyone,

I am doing Login webform (C# .NET web application) with SQL Server 2000.

The staff table is to store authenticated user info.

But when I test it, I found that the password can be case insensitive, i.e. 'A0001' should be correct password, but 'a0001' can allow login.

Could anyone tell me how to solve this problem??

Thanks you very much!!

private void btnLogin_Click(object sender, System.EventArgs e)
{
//instantiate SQL connection
SqlConnection sqlConnect = new SqlConnection(connectStg);
SqlCommand selectLogin = sqlConnect.CreateCommand();
selectLogin.CommandText = "SELECT sid, type from STAFF Where sid= '" + txtId.Text + "' and pwd= '" + txtPwd.Text + "' ";
//open connectin for execution
sqlConnect.Open();
//instantiate the SqlDataReader reader
SqlDataReader loginReader = selectLogin.ExecuteReader();
//try and catch SqlException error
try
{
if(loginReader.Read())
{
// check whether the user is the role of administrator or operator
// I use GetValue(1) i.e. type field from the above select statement // if "O' then go operator page, else go to administrator page.
if (loginReader.GetValue(1).ToString().ToUpper().Equals("O"))
{
Server.Transfer("//SMS/LoginUser/SuccessLoginOper.aspx");
}
else if (loginReader.GetValue(1).ToString().ToUpper().Equals("A"))
{
Server.Transfer("//SMS/LoginUser/SuccessLoginAdmin.aspx");
}
}
else
{
//clear content of textbox and display error message
txtId.Text="";
txtPwd.Text="";
lblLoginFail.Visible = true;
lblLoginFail.Text="Login Failed!<br>" + "Ensure that ID and Password are correct!";
}
}
catch (SqlException se)
{
if (se.Number == 17)
{
lblLoginFail.Visible = true;
lblLoginFail.Text = "Could not connect to the database";
}
else
{
lblLoginFail.Visible = true;
lblLoginFail.Text = se.Message;
}
}
//close SqlDataReader and SqlConnection
loginReader.Close();
sqlConnect.Close();

You can alter the database to be case sensitive, and I think you can also do that on a per connection basis - but you'd have to check that. The other way could be to return the passwords that have matched and then double check them in c#. I sure someone has a better method.|||Case-sesitivity is determined when installing SQL Server,
try running sp_help to see the current settings.
Passwords shouldn't be stored in plaintext in the database
anyway. I suggest you have a look at the hashing functions
in .Net and use them to calculate a hash and then save that
in the database.
Then you wouldn't have to worry about case-sensitivity either.|||Thanks you for reply!!

As you said running sp_help to see the current settings, how to change the current settings of case-sensitive problems.

I recognise that the passwords should be better stored in encrypted forms. But how to encrypt it in SQL Server. I am new in web development. Could you briefly tell me how to do? Or any web reference provided?

Waiting for reply! Thanks

Roy|||::As you said running sp_help to see the current settings, how to change the current settings
::of case-sensitive problems

He DID tell you it is determined on install time. So you can not change it.

::I recognise that the passwords should be better stored in encrypted forms.

Good. You are wrong, though. Storing encrypted passwords in SQL Server is as bad as storing them plain text. Hashing is not encryption.

::But how to encrypt it in SQL Server.

Why should you?

Hash (not encrypt) the passwords on the website, then store he hashed passwords in the server.

In the SQL only ask for the user's data by user name, retrieve the password hash from the server, hash the user input and compare. Do not forget to salt your hashes, as otherwise you are totally open to a dictionary attack.

::I am new in web development.

Not to development in general? Sounds more like this. I would suggest you invest heavily into some books.|||First of all you should be aware that the case-sensitivity settings are GLOBAL to the entire SQL Server and all databases on it.
If you really want to to the change you have to rebuild the master database using
Rebuildm.exe.
Do look it up in the books online first, and don't forget to backup your database before!

For hashing password have a look at the classes:
System.Security.Cryptography.MD5
or preferrably
System.Security.Cryptography.SHA1