Replace function
-
Hi, I am using SQL Server 2005 and have a question regarding the Replace function. I have a 'select' query looking something like this: SELECT tblContacts.* FROM tblContacts WHERE tblContacts.[Surname] + tblContacts.[Firstname] LIKE '%Smith (Hon. Member)John%' ('Smith (Hon. Member)John' will come from the user's input) Now, the end users do not want to have to type in 'Smith (Hon. Member)John', they just want to type SmithHonMemberJohn, that is, the full name and membership type without any spaces, brackets or full stops (periods). I have looked at the replace function to replace characters but is it possible to replace more than 1 type of character? That is, the replcae function will replace all instances of a certain character, for instance, commas. But is it possible to specify more than 1 chacter type, for instance commas, brackets and spaces? Hope I have explained this OK. Thanks in advance for any help.
-
Hi, I am using SQL Server 2005 and have a question regarding the Replace function. I have a 'select' query looking something like this: SELECT tblContacts.* FROM tblContacts WHERE tblContacts.[Surname] + tblContacts.[Firstname] LIKE '%Smith (Hon. Member)John%' ('Smith (Hon. Member)John' will come from the user's input) Now, the end users do not want to have to type in 'Smith (Hon. Member)John', they just want to type SmithHonMemberJohn, that is, the full name and membership type without any spaces, brackets or full stops (periods). I have looked at the replace function to replace characters but is it possible to replace more than 1 type of character? That is, the replcae function will replace all instances of a certain character, for instance, commas. But is it possible to specify more than 1 chacter type, for instance commas, brackets and spaces? Hope I have explained this OK. Thanks in advance for any help.
Not ideal, but you can nest the replace statments for a quick fix: replace(replace(replace(replace('Smith (Hon. Member)John','(',''),')',''),'.',''),' ','') produces: SmithHonMemberJohn otherwise you could always write a function to remove any non alphanumeric characters. I don't know of any pattern matching within the replace statement though.
-
Not ideal, but you can nest the replace statments for a quick fix: replace(replace(replace(replace('Smith (Hon. Member)John','(',''),')',''),'.',''),' ','') produces: SmithHonMemberJohn otherwise you could always write a function to remove any non alphanumeric characters. I don't know of any pattern matching within the replace statement though.
I like the idea of a SQL Server function to remove any non alpha characters but, my query is being dynamically built within the Windows application rather than being a stored procedure. Am I able to call a SQL Server function in this way? If not then the nested replace will have to suffice!
-
I like the idea of a SQL Server function to remove any non alpha characters but, my query is being dynamically built within the Windows application rather than being a stored procedure. Am I able to call a SQL Server function in this way? If not then the nested replace will have to suffice!
Yes, you can call it that way. If you make a SQL Server scalar function for this purpose, let's say it's named 'RemoveNonAlphaChars'. Your SELECT would use it like so:
SELECT tblContacts.*
FROM tblContacts
WHERE dbo.RemoveNonAlphaChars(tblContacts.[Surname] + tblContacts.[Firstname]) LIKE '%SmithHonMemberJohn%'Keep It Simple Stupid! (KISS)
-
Yes, you can call it that way. If you make a SQL Server scalar function for this purpose, let's say it's named 'RemoveNonAlphaChars'. Your SELECT would use it like so:
SELECT tblContacts.*
FROM tblContacts
WHERE dbo.RemoveNonAlphaChars(tblContacts.[Surname] + tblContacts.[Firstname]) LIKE '%SmithHonMemberJohn%'Keep It Simple Stupid! (KISS)
Great :) Thank you both for your help.