How to find a data in SQL Server with wrong blank space on the middle of it?

Question

I wanted to search some person on my data base (my application has been developed in c# and data base is Microsoft SQL server 2008).

User want to looking for this name "John Alan" :

Scenario 1: User entered "john alan", it's OK I can found it with my SP on Database
Scenario 2: User entered "johnalan" whit no space between name and family
Scenario 3: User entered "joh nalan" whit a wrong space place

I have no problem with scenario but how can handle numbers 2 and 3?

I did some search on the internet but I did not know what should I exactly looking for, If is that a Technic or algorithm or kind of T-SQL query or c# code please give me a name or help me.

My table has two columns:

Name       Family
-------   -------
john       alan

Could not not just replace whitespace with nothing - e.g. where replace(SomeColumn, ' ', '') = @ValueWithoutSpaces — NDJ, Jul 08 '19 at 09:00
@chetan-ranpariya ,I saw a very simple windows application can handle it an does not use AI — motevalizadeh, Jul 08 '19 at 09:01
Replace the whitespaces in both the user input and the name+family before comparing them. — EzLo, Jul 08 '19 at 09:02
@Ezlo It seems your solution can handle it, I'm working on it — motevalizadeh, Jul 08 '19 at 09:03
why dont you `where Name + Family = replace(@input,' ', '')`? — Cleptus, Jul 08 '19 at 09:04
this might be helpful: https://stackoverflow.com/questions/5374481/like-operator-in-linq — Md. Tazbir Ur Rahman Bhuiyan, Jul 08 '19 at 09:22

score 1 · Accepted Answer · answered Jul 08 '19 at 09:24

You could do this by removing spaces from name and family. T-SQL Replace will remove specific spaces from your string, If user uses N number of spaces then solution By using Replace will fail. So that you can use following code

  select   '"' + Concat(name,family) + '"', '"' + replace(Concat(name,family), ' ', '') + '"' AS nameandfamily

For more please check this link

score 1 · Answer 2 · answered Jul 08 '19 at 09:45

I would suggest you to remove white spaces of the name in C#.
```
 Regex.Replace(strFullName, @"\s+", "")
```
Modify the table to add additional persisted computed column to have the HASH value of the fullname, as given below

CREATE TABLE #family(name varchar(10), family varchar(20))

    INSERT INTO #family 
    values ('john','alan'), ('johnalan',''),('joh','nalan')

    ALTER TABLE #family
    ADD FullNamehash AS CHECKSUM(concat(LTRIM(RTRIM(name)), LTRIM(RTRIM(family))))  PERSISTED

Write a stored procedure, which accepts fullName as parameter, which returns the family details as given below. You can read more about CHECKSUM

CREATE PROCEDURE ReturnFamilyDetails (@fullName VARCHAR(30))
    AS
    BEGIN
    DECLARE @hash_fullname int = CHECKSUM(@fullName)
    SELECT name,family from #family where fullNamehash = @hash_fullname
    END

score 0 · Answer 3 · answered Jul 08 '19 at 09:17

If you want search only this simple scenarios you can concatenate 2 columns and use LIKE for concatenated user query

userquery = Regex.Replace(userquery, @"\s+", "").ToLower();
..
where Regex.Replace(query.Name+query.Family, @"\s+", "").ToLower() LIKE userquery

How to find a data in SQL Server with wrong blank space on the middle of it?

3 Answers3