TSQL function to compare two strings

compare two string columns in sql server
sql server compare two text fields
sql server compare similar strings
sql compare strings not equal
sql server string comparison ignore spaces
sql server text compare
compare two strings in oracle query
sql longest common substring

Can anyone give me some sample TSQL code to compare two email addresses to check they are equal or not?

CLR functions are not an option. I tried that but our DBA for some reason is totally against using CLR functions in SSMS.

I know how to get the domain names (eg: mycompany.com) from the email address.

Really appreciate any suggestions Thanks in advance

Not really sure what you are looking for. From your question, I understand that you need to check 2 email addresses for similarity / dissimilarity.

Why can you not use this?

declare @email1 varchar(100) set @email1 = 'billg@microsoft.com'
declare @email2 varchar(100) set @email2 = 'melinda@microsoft.com'
IF
@email1=@email2
BEGIN
    PRINT 'Same Email'
END
ELSE
BEGIN
    PRINT 'Not Same Email'
END

Raj

DIFFERENCE (Transact-SQL), This function returns an integer value measuring the difference DIFFERENCE compares two different SOUNDEX values, and returns The first part of this example compares the SOUNDEX values of two very similar strings. Ultimately, you appear to be looking to solve for the likelihood that two strings are a "fuzzy" match to one another. SQL provides efficient, optimized built-in functions that will do that for you, and likely with better performance than what you have written. The two functions you are looking for are SOUNDEX and DIFFERENCE.

In SQL Server 2005+ use function CHECKSUM()

CHECKSUM computes a hash value, called the checksum, over its list of arguments. The hash value is intended for use in building hash indexes. If the arguments to CHECKSUM are columns, and an index is built over the computed CHECKSUM value, the result is a hash index. This can be used for equality searches over the columns. More info about CHECKSUM()

DECLARE @email1 varchar(100) = 'billg@microsoft.com'
DECLARE @email2 varchar(100) = 'melinda@microsoft.com'
SELECT CASE WHEN CHECKSUM(@email1) = CHECKSUM(@email2) THEN 'Same Email' 
                                                       ELSE 'Different Email' END

Quantifying Text differences in TSQL - Simple Talk, When you compare two pieces of text, or strings, in SQL Server in an 'parsing' table-function, I then do a simple outer join between the two  The function decompose strings into table variables and check if count of distinct characters is same and if join between two tables returns same count. So it treats 761 and 16767 as same strings. If you want that strings be equal by length just remove distinct

Test whether two email addresses have the same domain:

declare @email1 varchar(100) set @email1 = 'billg@microsoft.com'
declare @email2 varchar(100) set @email2 = 'melinda@microsoft.com'

select 
  case when 
    right(@email1, len(@email1) - charindex('@', @email1)) =
    right(@email2, len(@email2) - charindex('@', @email2))
  then 'Same domain'
  else 'Different domains'
  end

String Comparisons in SQL: The Longest Common Substring , I've always wanted a SQL function that tells me the longest substring shared between two strings. Of course, you can use add-in components such as full-​text search to do the job for you in SQL Server In the case of string comparison, you can very quickly get bogged down unless you 'think relationally'. Accepts two strings Breaks them into two arrays using the split function on " " Compare the contents of the two arrays, returning true if they contain the same elements. If this is a route you'd like to go, take a look at this article to get started on creating CLR UDFs.

Though this is an old post, I think it is important to comment about the solution using CHECKSUM. By definition, checksums belong to a finite space thus having a finite number of different values. With a 32 bits number, there are 4,294,967,295 possible values. I am tempted to say there are ONLY 4,294,967,295 possible values leaving place to potential collisions when two email address yield the same checksum. The pure string comparison (email1 = email2) - as suggested by Raj - prevents such collision as by their nature email addresses are unique.

cf: [https://msdn.microsoft.com/en-us/library/ms189788(v=SQL.100).aspx?ranMID=24542&ranEAID=TnL5HPStwNw&ranSiteID=TnL5HPStwNw-pDIZxu_YsTXD2vBagzBEKA&tduid=(e03e708849bdae31622c749be9e951f9)(256380)(2459594)(TnL5HPStwNw-pDIZxu_YsTXD2vBagzBEKA)()][1]

[http://preshing.com/20110504/hash-collision-probabilities/][2]

Understanding SQL Server DIFFERENCE() Function By Examples, In this tutorial, you will learn how to use the SQL Server DIFFERENCE() function to compare two SOUNDEX() values of two strings. In this blog, I’ll tackle the Longest Common Substring, which is a common, and useful, function that tells you the longest common substring between two strings. If you, for example, were to compare ‘And the Dish ran away with the Spoon’ with ‘away’, you’d get ‘away’ as being the string in common. Likewise, comparing ‘465932859472109683472’ with ‘697834859472135348’ would give you ‘8594721’.

--what about patindex() function? -- small example:

create table t1 (tkey integer, val nvarchar(20) )  
create table t2 (tkey integer, val nvarchar(20) )  
insert into t1 (tkey, val) values (1, 'abc' )  
insert into t1 (tkey, val) values (2, 'efgh' )  
insert into t1 (tkey, val) values (3, 'xyz' )  

insert into t2 (tkey, val) values (1, 'abc' )  
insert into t2 (tkey, val) values (2, '  efgh' )  
insert into t2 (tkey, val) values (3, 'xy z' )  


select t1.val, t2.val, patindex( t1.val, t2.val )  
from t1, t2  
where t1.tkey = t2.tkey  

How to Compare Strings In Two Different VARCHAR Columns , the content) from the lastname column if it exists in the firstname column. How can this be done via a UDF or case statement in SQL Server 2008 R2? share. I need to help to write a query to show the difference between two string in two table. table1. @strng1 = 'This is SQL Server 2008R2' table2. @strng2 = 'This is a SQL Server 2008R2' now i have to compare @strng1 with @strng2. differnce = "a" thanks in advance..

MySQL STRCMP() Function, The STRCMP() function compares two strings. Syntax. STRCMP(string1, string2). Parameter Values. Parameter, Description. string1, string2, Required. To see the performance difference, try this: SELECT count(*) FROM master..sysobjects as A JOIN tempdb..sysobjects as B on A.name = B.name SELECT count(*) FROM master..sysobjects as A JOIN tempdb..sysobjects as B on A.name LIKE B.name Comparing strings with '=' is much faster.

MySQL STRCMP() function, MySQL STRCMP() is used to compare two strings and returns 0 if both of the strings are same and returns -1 when the first argument is smaller  Both strings must be split into their parts. In SQL-Server 2008 this is best to be done with an XML approach. attention: If your data might include forbidden characters like <>öä@€& and not just plain latin characters like in your example, you'd need some extra effort

Beware of this trap when comparing strings in T-SQL with trailing , If you are going to do this, for God's sake please comment the reason why, or better yet wrap it in a string comparison user defined function. Some  SQL Server Functions. Compare two strings: The STRCMP() function compares two strings. Syntax. STRCMP(string1, string2) Parameter Values.

Comments
  • err, you don't need a UDF to compare two strings for equality - what's wrong with using = ?
  • By compare you mean similarity or equality?
  • Your question says you know how to get the domain names from the email address - so just compare those strings? Or was that a typo and you meant to say you don't know?
  • Very good simple implementation but does not account for CI vs CS. Use checksum for granular needs, as stated below by Aleksandr.
  • Thank you all for your answers.I got what i was looking for
  • Welcome to stack overflow, I'd like to suggest you to format code as code. You can do so selecting the code and press control + k or also indenting it by 4 spaces or by inserting it between two backticks (alt + 96)