Mixing
sql: words match separately, find out the spelling is partly different
1
Does anyone have a idea to matches below 2 situation?
Data:
ID| NAMES
1 | ASHRA MUHAMMAD YUSU
2 | ASHRAF MUHAMMAD YUSUF
3 | YUSUF UTHMAN ABD
=====================
I'm using SqlServer2008, and
There are 2 user requirements:
(1) Find out the spelling is partly different
For example: the user enters "ASHRA YUSU", the following is my expected result:
ID| NAMES
2 | ASHRAF MUHAMMAD YUSUF
(2) Find out the name is exactly the same, allow the order is different
For example: the user enters "ASHRA YUSU", the following is my expected result:
ID| NAMES
1 | ASHRA MUHAMMAD YUSU
sql
sql-server tsql edited Nov 20 '18 at 8:32
Rahul Neekhra
6021627
asked Nov 20 '18 at 4:27
user1625812user1625812
474
add a comment |
1
Does anyone have a idea to matches below 2 situation?
Data:
ID| NAMES
1 | ASHRA MUHAMMAD YUSU
2 | ASHRAF MUHAMMAD YUSUF
3 | YUSUF UTHMAN ABD
=====================
I'm using SqlServer2008, and
There are 2 user requirements:
(1) Find out the spelling is partly different
For example: the user enters "ASHRA YUSU", the following is my expected result:
ID| NAMES
2 | ASHRAF MUHAMMAD YUSUF
(2) Find out the name is exactly the same, allow the order is different
For example: the user enters "ASHRA YUSU", the following is my expected result:
ID| NAMES
1 | ASHRA MUHAMMAD YUSU
sql
sql-server tsql edited Nov 20 '18 at 8:32
Rahul Neekhra
6021627
asked Nov 20 '18 at 4:27
user1625812user1625812
474
In your first case you don't want to include exactly same result?
– tejash patel
Nov 20 '18 at 4:35
I did this many years ago. The way to do is doing a table-valued function, that split in space. It returns 1 row = 1 word. You then only need to to a regular join.
– DanB
Nov 20 '18 at 4:36
Are all the names 3 parts ?
– Sufyan Jabr
Nov 20 '18 at 5:16
hi, tejash patel , for 1st case , "exactly same" is not the expected result.
– user1625812
Nov 20 '18 at 7:29
add a comment |
1
1
1
Does anyone have a idea to matches below 2 situation?
Data:
ID| NAMES
1 | ASHRA MUHAMMAD YUSU
2 | ASHRAF MUHAMMAD YUSUF
3 | YUSUF UTHMAN ABD
=====================
I'm using SqlServer2008, and
There are 2 user requirements:
(1) Find out the spelling is partly different
For example: the user enters "ASHRA YUSU", the following is my expected result:
ID| NAMES
2 | ASHRAF MUHAMMAD YUSUF
(2) Find out the name is exactly the same, allow the order is different
For example: the user enters "ASHRA YUSU", the following is my expected result:
ID| NAMES
1 | ASHRA MUHAMMAD YUSU
sql
sql-server tsql edited Nov 20 '18 at 8:32
Rahul Neekhra
6021627
asked Nov 20 '18 at 4:27
user1625812user1625812
474
Does anyone have a idea to matches below 2 situation?
Data:
ID| NAMES
1 | ASHRA MUHAMMAD YUSU
2 | ASHRAF MUHAMMAD YUSUF
3 | YUSUF UTHMAN ABD
=====================
I'm using SqlServer2008, and
There are 2 user requirements:
(1) Find out the spelling is partly different
For example: the user enters "ASHRA YUSU", the following is my expected result:
ID| NAMES
2 | ASHRAF MUHAMMAD YUSUF
(2) Find out the name is exactly the same, allow the order is different
For example: the user enters "ASHRA YUSU", the following is my expected result:
ID| NAMES
1 | ASHRA MUHAMMAD YUSU
sql
sql-server tsql sql
sql-server tsql edited Nov 20 '18 at 8:32
Rahul Neekhra
6021627
asked Nov 20 '18 at 4:27
user1625812user1625812
474
edited Nov 20 '18 at 8:32
Rahul Neekhra
6021627
asked Nov 20 '18 at 4:27
user1625812user1625812
474
edited Nov 20 '18 at 8:32
Rahul Neekhra
6021627
edited Nov 20 '18 at 8:32
Rahul Neekhra
6021627
edited Nov 20 '18 at 8:32
Rahul Neekhra
6021627
6021627
asked Nov 20 '18 at 4:27
user1625812user1625812
474
asked Nov 20 '18 at 4:27
user1625812user1625812
474
asked Nov 20 '18 at 4:27
user1625812user1625812
474
474
In your first case you don't want to include exactly same result?
– tejash patel
Nov 20 '18 at 4:35
I did this many years ago. The way to do is doing a table-valued function, that split in space. It returns 1 row = 1 word. You then only need to to a regular join.
– DanB
Nov 20 '18 at 4:36
Are all the names 3 parts ?
– Sufyan Jabr
Nov 20 '18 at 5:16
hi, tejash patel , for 1st case , "exactly same" is not the expected result.
– user1625812
Nov 20 '18 at 7:29
add a comment |
In your first case you don't want to include exactly same result?
– tejash patel
Nov 20 '18 at 4:35
I did this many years ago. The way to do is doing a table-valued function, that split in space. It returns 1 row = 1 word. You then only need to to a regular join.
– DanB
Nov 20 '18 at 4:36
Are all the names 3 parts ?
– Sufyan Jabr
Nov 20 '18 at 5:16
hi, tejash patel , for 1st case , "exactly same" is not the expected result.
– user1625812
Nov 20 '18 at 7:29
In your first case you don't want to include exactly same result?
– tejash patel
Nov 20 '18 at 4:35
In your first case you don't want to include exactly same result?
– tejash patel
Nov 20 '18 at 4:35
I did this many years ago. The way to do is doing a table-valued function, that split in space. It returns 1 row = 1 word. You then only need to to a regular join.
– DanB
Nov 20 '18 at 4:36
I did this many years ago. The way to do is doing a table-valued function, that split in space. It returns 1 row = 1 word. You then only need to to a regular join.
– DanB
Nov 20 '18 at 4:36
Are all the names 3 parts ?
– Sufyan Jabr
Nov 20 '18 at 5:16
Are all the names 3 parts ?
– Sufyan Jabr
Nov 20 '18 at 5:16
hi, tejash patel , for 1st case , "exactly same" is not the expected result.
– user1625812
Nov 20 '18 at 7:29
hi, tejash patel , for 1st case , "exactly same" is not the expected result.
– user1625812
Nov 20 '18 at 7:29
add a comment |
1 Answer
1
active
oldest
votes
0
Try this :
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
go
;with tmp as (select <table>.id, splitdata
from <table> cross apply dbo.fnSplitString(<table>.name, ' '))
, tmp2 as (select distinct <table>.id, count(*) cnt
from dbo.fnSplitString(<search string>, ' ') tmp2
inner join tmp on tmp.splitdata = tmp2.splitData
group by tmp.id)
select <table>.*, cnt from <table> inner join tmp2 on <table>.Id = tmp2.Id;
answered Nov 20 '18 at 4:49
DanBDanB
1,4691114
I wait for the day, where people stop posting string splitters withWHILEor any other kind of procedural loops...
– Shnugo
Nov 20 '18 at 10:00
Shnugo, what is your solution?
– DanB
Nov 20 '18 at 14:29
Dan, with SQL-Server 2017+ there isSTRING_SPLIT()(or even betterOPENJSON()). With former versions there are several approaches using a transformation to XML, recursive CTEs or tally/number tables. There are many answers on SO covering this, a good overview is this one. Just ignore all approaches withWHILE, multi-statement UDF or scalar functions... And you might read this great article serie by Aaron Bertrand
– Shnugo
Nov 20 '18 at 14:38
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53386224%2fsql-words-match-separately-find-out-the-spelling-is-partly-different%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Try this :
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
go
;with tmp as (select <table>.id, splitdata
from <table> cross apply dbo.fnSplitString(<table>.name, ' '))
, tmp2 as (select distinct <table>.id, count(*) cnt
from dbo.fnSplitString(<search string>, ' ') tmp2
inner join tmp on tmp.splitdata = tmp2.splitData
group by tmp.id)
select <table>.*, cnt from <table> inner join tmp2 on <table>.Id = tmp2.Id;
answered Nov 20 '18 at 4:49
DanBDanB
1,4691114
I wait for the day, where people stop posting string splitters withWHILEor any other kind of procedural loops...
– Shnugo
Nov 20 '18 at 10:00
Shnugo, what is your solution?
– DanB
Nov 20 '18 at 14:29
Dan, with SQL-Server 2017+ there isSTRING_SPLIT()(or even betterOPENJSON()). With former versions there are several approaches using a transformation to XML, recursive CTEs or tally/number tables. There are many answers on SO covering this, a good overview is this one. Just ignore all approaches withWHILE, multi-statement UDF or scalar functions... And you might read this great article serie by Aaron Bertrand
– Shnugo
Nov 20 '18 at 14:38
add a comment |
0
Try this :
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
go
;with tmp as (select <table>.id, splitdata
from <table> cross apply dbo.fnSplitString(<table>.name, ' '))
, tmp2 as (select distinct <table>.id, count(*) cnt
from dbo.fnSplitString(<search string>, ' ') tmp2
inner join tmp on tmp.splitdata = tmp2.splitData
group by tmp.id)
select <table>.*, cnt from <table> inner join tmp2 on <table>.Id = tmp2.Id;
answered Nov 20 '18 at 4:49
DanBDanB
1,4691114
I wait for the day, where people stop posting string splitters withWHILEor any other kind of procedural loops...
– Shnugo
Nov 20 '18 at 10:00
Shnugo, what is your solution?
– DanB
Nov 20 '18 at 14:29
Dan, with SQL-Server 2017+ there isSTRING_SPLIT()(or even betterOPENJSON()). With former versions there are several approaches using a transformation to XML, recursive CTEs or tally/number tables. There are many answers on SO covering this, a good overview is this one. Just ignore all approaches withWHILE, multi-statement UDF or scalar functions... And you might read this great article serie by Aaron Bertrand
– Shnugo
Nov 20 '18 at 14:38
add a comment |
0
0
0
Try this :
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
go
;with tmp as (select <table>.id, splitdata
from <table> cross apply dbo.fnSplitString(<table>.name, ' '))
, tmp2 as (select distinct <table>.id, count(*) cnt
from dbo.fnSplitString(<search string>, ' ') tmp2
inner join tmp on tmp.splitdata = tmp2.splitData
group by tmp.id)
select <table>.*, cnt from <table> inner join tmp2 on <table>.Id = tmp2.Id;
answered Nov 20 '18 at 4:49
DanBDanB
1,4691114
Try this :
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
go
;with tmp as (select <table>.id, splitdata
from <table> cross apply dbo.fnSplitString(<table>.name, ' '))
, tmp2 as (select distinct <table>.id, count(*) cnt
from dbo.fnSplitString(<search string>, ' ') tmp2
inner join tmp on tmp.splitdata = tmp2.splitData
group by tmp.id)
select <table>.*, cnt from <table> inner join tmp2 on <table>.Id = tmp2.Id;
answered Nov 20 '18 at 4:49
DanBDanB
1,4691114
answered Nov 20 '18 at 4:49
DanBDanB
1,4691114
answered Nov 20 '18 at 4:49
DanBDanB
1,4691114
answered Nov 20 '18 at 4:49
DanBDanB
1,4691114
1,4691114
I wait for the day, where people stop posting string splitters withWHILEor any other kind of procedural loops...
– Shnugo
Nov 20 '18 at 10:00
Shnugo, what is your solution?
– DanB
Nov 20 '18 at 14:29
Dan, with SQL-Server 2017+ there isSTRING_SPLIT()(or even betterOPENJSON()). With former versions there are several approaches using a transformation to XML, recursive CTEs or tally/number tables. There are many answers on SO covering this, a good overview is this one. Just ignore all approaches withWHILE, multi-statement UDF or scalar functions... And you might read this great article serie by Aaron Bertrand
– Shnugo
Nov 20 '18 at 14:38
add a comment |
I wait for the day, where people stop posting string splitters withWHILEor any other kind of procedural loops...
– Shnugo
Nov 20 '18 at 10:00
Shnugo, what is your solution?
– DanB
Nov 20 '18 at 14:29
Dan, with SQL-Server 2017+ there isSTRING_SPLIT()(or even betterOPENJSON()). With former versions there are several approaches using a transformation to XML, recursive CTEs or tally/number tables. There are many answers on SO covering this, a good overview is this one. Just ignore all approaches withWHILE, multi-statement UDF or scalar functions... And you might read this great article serie by Aaron Bertrand
– Shnugo
Nov 20 '18 at 14:38
I wait for the day, where people stop posting string splitters with
WHILE or any other kind of procedural loops...– Shnugo
Nov 20 '18 at 10:00
I wait for the day, where people stop posting string splitters with
WHILE or any other kind of procedural loops...– Shnugo
Nov 20 '18 at 10:00
Shnugo, what is your solution?
– DanB
Nov 20 '18 at 14:29
Shnugo, what is your solution?
– DanB
Nov 20 '18 at 14:29
Dan, with SQL-Server 2017+ there is
STRING_SPLIT() (or even better OPENJSON()). With former versions there are several approaches using a transformation to XML, recursive CTEs or tally/number tables. There are many answers on SO covering this, a good overview is this one. Just ignore all approaches with WHILE, multi-statement UDF or scalar functions... And you might read this great article serie by Aaron Bertrand– Shnugo
Nov 20 '18 at 14:38
Dan, with SQL-Server 2017+ there is
STRING_SPLIT() (or even better OPENJSON()). With former versions there are several approaches using a transformation to XML, recursive CTEs or tally/number tables. There are many answers on SO covering this, a good overview is this one. Just ignore all approaches with WHILE, multi-statement UDF or scalar functions... And you might read this great article serie by Aaron Bertrand– Shnugo
Nov 20 '18 at 14:38
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53386224%2fsql-words-match-separately-find-out-the-spelling-is-partly-different%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
In your first case you don't want to include exactly same result?
– tejash patel
Nov 20 '18 at 4:35
I did this many years ago. The way to do is doing a table-valued function, that split in space. It returns 1 row = 1 word. You then only need to to a regular join.
– DanB
Nov 20 '18 at 4:36
Are all the names 3 parts ?
– Sufyan Jabr
Nov 20 '18 at 5:16
hi, tejash patel , for 1st case , "exactly same" is not the expected result.
– user1625812
Nov 20 '18 at 7:29