Calculate percentage of matching word
CREATE TABLE tbl_pat
(
id int,
name varchar(100),
[address] varchar(500)
);
INSERT INTO tbl_pat VALUES(1,'Jack','Lane 1, 90 Road Street, SL');
INSERT INTO tbl_pat VALUES(2,'Will','SA, Lane 10, Street road');
INSERT INTO tbl_pat VALUES(3,'White','Lane 1 ZIM');
INSERT INTO tbl_pat VALUES(4,'Shaw','Street Road');
INSERT INTO tbl_pat VALUES(5,'Steve','Road Street');
INSERT INTO tbl_pat VALUES(6,'Brown','Nz Road 10');
Expected Result:
Search string is : Street Road
Name Address Percentage
---------------------------------------------
Shaw Street Road 100
Steve Road Street 100
Will SA, Lane 10, Street road 20
Jack Lane 1, 90 Road Street, SL 17
Note: The percentage are mentioned on assumption, but the first two should be 100% percent as it has exact match.
I am using PATINDEX for searching the word.
Query: Searching for Street road
SELECT [Name],[Address]
FROM tbl_pat
WHERE PATINDEX('%Street%',[Address])>=1 AND PATINDEX('%Road%',[Address])>=1
How to calculate the percentage of the matching word in the single select statement?
sql-server sql-server-2008-r2 asked Nov 15 '18 at 8:17
MAKMAK
2,13263478
add a comment |
CREATE TABLE tbl_pat
(
id int,
name varchar(100),
[address] varchar(500)
);
INSERT INTO tbl_pat VALUES(1,'Jack','Lane 1, 90 Road Street, SL');
INSERT INTO tbl_pat VALUES(2,'Will','SA, Lane 10, Street road');
INSERT INTO tbl_pat VALUES(3,'White','Lane 1 ZIM');
INSERT INTO tbl_pat VALUES(4,'Shaw','Street Road');
INSERT INTO tbl_pat VALUES(5,'Steve','Road Street');
INSERT INTO tbl_pat VALUES(6,'Brown','Nz Road 10');
Expected Result:
Search string is : Street Road
Name Address Percentage
---------------------------------------------
Shaw Street Road 100
Steve Road Street 100
Will SA, Lane 10, Street road 20
Jack Lane 1, 90 Road Street, SL 17
Note: The percentage are mentioned on assumption, but the first two should be 100% percent as it has exact match.
I am using PATINDEX for searching the word.
Query: Searching for Street road
SELECT [Name],[Address]
FROM tbl_pat
WHERE PATINDEX('%Street%',[Address])>=1 AND PATINDEX('%Road%',[Address])>=1
How to calculate the percentage of the matching word in the single select statement?
sql-server sql-server-2008-r2 asked Nov 15 '18 at 8:17
MAKMAK
2,13263478
What's the formula for calculating this percentage?
– gotqn
Nov 15 '18 at 8:20
This will be hard to do accurately without the ability to do full regex searches.
– Tim Biegeleisen
Nov 15 '18 at 8:23
add a comment |
CREATE TABLE tbl_pat
(
id int,
name varchar(100),
[address] varchar(500)
);
INSERT INTO tbl_pat VALUES(1,'Jack','Lane 1, 90 Road Street, SL');
INSERT INTO tbl_pat VALUES(2,'Will','SA, Lane 10, Street road');
INSERT INTO tbl_pat VALUES(3,'White','Lane 1 ZIM');
INSERT INTO tbl_pat VALUES(4,'Shaw','Street Road');
INSERT INTO tbl_pat VALUES(5,'Steve','Road Street');
INSERT INTO tbl_pat VALUES(6,'Brown','Nz Road 10');
Expected Result:
Search string is : Street Road
Name Address Percentage
---------------------------------------------
Shaw Street Road 100
Steve Road Street 100
Will SA, Lane 10, Street road 20
Jack Lane 1, 90 Road Street, SL 17
Note: The percentage are mentioned on assumption, but the first two should be 100% percent as it has exact match.
I am using PATINDEX for searching the word.
Query: Searching for Street road
SELECT [Name],[Address]
FROM tbl_pat
WHERE PATINDEX('%Street%',[Address])>=1 AND PATINDEX('%Road%',[Address])>=1
How to calculate the percentage of the matching word in the single select statement?
sql-server sql-server-2008-r2 asked Nov 15 '18 at 8:17
MAKMAK
2,13263478
CREATE TABLE tbl_pat
(
id int,
name varchar(100),
[address] varchar(500)
);
INSERT INTO tbl_pat VALUES(1,'Jack','Lane 1, 90 Road Street, SL');
INSERT INTO tbl_pat VALUES(2,'Will','SA, Lane 10, Street road');
INSERT INTO tbl_pat VALUES(3,'White','Lane 1 ZIM');
INSERT INTO tbl_pat VALUES(4,'Shaw','Street Road');
INSERT INTO tbl_pat VALUES(5,'Steve','Road Street');
INSERT INTO tbl_pat VALUES(6,'Brown','Nz Road 10');
Expected Result:
Search string is : Street Road
Name Address Percentage
---------------------------------------------
Shaw Street Road 100
Steve Road Street 100
Will SA, Lane 10, Street road 20
Jack Lane 1, 90 Road Street, SL 17
Note: The percentage are mentioned on assumption, but the first two should be 100% percent as it has exact match.
I am using PATINDEX for searching the word.
Query: Searching for Street road
SELECT [Name],[Address]
FROM tbl_pat
WHERE PATINDEX('%Street%',[Address])>=1 AND PATINDEX('%Road%',[Address])>=1
How to calculate the percentage of the matching word in the single select statement?
sql-server sql-server-2008-r2 sql-server sql-server-2008-r2 asked Nov 15 '18 at 8:17
MAKMAK
2,13263478
asked Nov 15 '18 at 8:17
MAKMAK
2,13263478
edited Nov 15 '18 at 8:26
MAK
asked Nov 15 '18 at 8:17
MAKMAK
2,13263478
asked Nov 15 '18 at 8:17
MAKMAK
2,13263478
asked Nov 15 '18 at 8:17
MAKMAK
2,13263478
2,13263478
What's the formula for calculating this percentage?
– gotqn
Nov 15 '18 at 8:20
This will be hard to do accurately without the ability to do full regex searches.
– Tim Biegeleisen
Nov 15 '18 at 8:23
add a comment |
What's the formula for calculating this percentage?
– gotqn
Nov 15 '18 at 8:20
This will be hard to do accurately without the ability to do full regex searches.
– Tim Biegeleisen
Nov 15 '18 at 8:23
What's the formula for calculating this percentage?
– gotqn
Nov 15 '18 at 8:20
What's the formula for calculating this percentage?
– gotqn
Nov 15 '18 at 8:20
This will be hard to do accurately without the ability to do full regex searches.
– Tim Biegeleisen
Nov 15 '18 at 8:23
This will be hard to do accurately without the ability to do full regex searches.
– Tim Biegeleisen
Nov 15 '18 at 8:23
add a comment |
2 Answers
2
active
oldest
votes
I think there is a problem with the percentage calculation expected you have provided
For example Jack, has a match of 2 output 6 words . So I expect its result to be %33
Please test following SQL query where I used String_Split function to split text into words
declare @str nvarchar(max) = 'Road Street'
; with tbl as (
select *, count(*) over (partition by id) word_count
from tbl_pat t
cross apply STRING_SPLIT(replace(t.address,',',' '), ' ')
where trim([value]) <> ''
)
select distinct id, [name], word_count, count(search.[value]) over (partition by id),
convert( decimal(5,2), (100.0 * (count(search.[value]) over (partition by id)) / word_count))
from tbl
left join (
select * from STRING_SPLIT(@str, ' ')
) search
on search.[value] = tbl.[value]
order by id
output is
answered Nov 15 '18 at 8:46
EralperEralper
5,25011221
add a comment |
here i am using string_split() from SQL Server 2017. You can replace with any string split function available. Just do a search
It is not perfect but it works for your sample.
select p.id, p.name, p.address, count(k.value) * 100.0 / count(*) as pecentage
from tbl_pat p
cross apply string_split(replace([address], ',', ' '), ' ') w
left join
(
select value
from string_split ('Road Street', ' ')
) k on w.value = k.value
group by p.id, p.name, p.address
answered Nov 15 '18 at 8:35
SquirrelSquirrel
11.9k22128
Hi @Squirrel, it will produce correct results if you add filter criteria " where w.value <> '' " to remove empty space returns of split function
– Eralper
Nov 15 '18 at 8:54
@Eralper. Good point ! thanks
– Squirrel
Nov 15 '18 at 8:59
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
});
}
});
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%2f53315027%2fcalculate-percentage-of-matching-word%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think there is a problem with the percentage calculation expected you have provided
For example Jack, has a match of 2 output 6 words . So I expect its result to be %33
Please test following SQL query where I used String_Split function to split text into words
declare @str nvarchar(max) = 'Road Street'
; with tbl as (
select *, count(*) over (partition by id) word_count
from tbl_pat t
cross apply STRING_SPLIT(replace(t.address,',',' '), ' ')
where trim([value]) <> ''
)
select distinct id, [name], word_count, count(search.[value]) over (partition by id),
convert( decimal(5,2), (100.0 * (count(search.[value]) over (partition by id)) / word_count))
from tbl
left join (
select * from STRING_SPLIT(@str, ' ')
) search
on search.[value] = tbl.[value]
order by id
output is
answered Nov 15 '18 at 8:46
EralperEralper
5,25011221
add a comment |
I think there is a problem with the percentage calculation expected you have provided
For example Jack, has a match of 2 output 6 words . So I expect its result to be %33
Please test following SQL query where I used String_Split function to split text into words
declare @str nvarchar(max) = 'Road Street'
; with tbl as (
select *, count(*) over (partition by id) word_count
from tbl_pat t
cross apply STRING_SPLIT(replace(t.address,',',' '), ' ')
where trim([value]) <> ''
)
select distinct id, [name], word_count, count(search.[value]) over (partition by id),
convert( decimal(5,2), (100.0 * (count(search.[value]) over (partition by id)) / word_count))
from tbl
left join (
select * from STRING_SPLIT(@str, ' ')
) search
on search.[value] = tbl.[value]
order by id
output is
answered Nov 15 '18 at 8:46
EralperEralper
5,25011221
add a comment |
I think there is a problem with the percentage calculation expected you have provided
For example Jack, has a match of 2 output 6 words . So I expect its result to be %33
Please test following SQL query where I used String_Split function to split text into words
declare @str nvarchar(max) = 'Road Street'
; with tbl as (
select *, count(*) over (partition by id) word_count
from tbl_pat t
cross apply STRING_SPLIT(replace(t.address,',',' '), ' ')
where trim([value]) <> ''
)
select distinct id, [name], word_count, count(search.[value]) over (partition by id),
convert( decimal(5,2), (100.0 * (count(search.[value]) over (partition by id)) / word_count))
from tbl
left join (
select * from STRING_SPLIT(@str, ' ')
) search
on search.[value] = tbl.[value]
order by id
output is
answered Nov 15 '18 at 8:46
EralperEralper
5,25011221
I think there is a problem with the percentage calculation expected you have provided
For example Jack, has a match of 2 output 6 words . So I expect its result to be %33
Please test following SQL query where I used String_Split function to split text into words
declare @str nvarchar(max) = 'Road Street'
; with tbl as (
select *, count(*) over (partition by id) word_count
from tbl_pat t
cross apply STRING_SPLIT(replace(t.address,',',' '), ' ')
where trim([value]) <> ''
)
select distinct id, [name], word_count, count(search.[value]) over (partition by id),
convert( decimal(5,2), (100.0 * (count(search.[value]) over (partition by id)) / word_count))
from tbl
left join (
select * from STRING_SPLIT(@str, ' ')
) search
on search.[value] = tbl.[value]
order by id
output is
answered Nov 15 '18 at 8:46
EralperEralper
5,25011221
answered Nov 15 '18 at 8:46
EralperEralper
5,25011221
answered Nov 15 '18 at 8:46
EralperEralper
5,25011221
answered Nov 15 '18 at 8:46
EralperEralper
5,25011221
5,25011221
add a comment |
add a comment |
here i am using string_split() from SQL Server 2017. You can replace with any string split function available. Just do a search
It is not perfect but it works for your sample.
select p.id, p.name, p.address, count(k.value) * 100.0 / count(*) as pecentage
from tbl_pat p
cross apply string_split(replace([address], ',', ' '), ' ') w
left join
(
select value
from string_split ('Road Street', ' ')
) k on w.value = k.value
group by p.id, p.name, p.address
answered Nov 15 '18 at 8:35
SquirrelSquirrel
11.9k22128
Hi @Squirrel, it will produce correct results if you add filter criteria " where w.value <> '' " to remove empty space returns of split function
– Eralper
Nov 15 '18 at 8:54
@Eralper. Good point ! thanks
– Squirrel
Nov 15 '18 at 8:59
add a comment |
here i am using string_split() from SQL Server 2017. You can replace with any string split function available. Just do a search
It is not perfect but it works for your sample.
select p.id, p.name, p.address, count(k.value) * 100.0 / count(*) as pecentage
from tbl_pat p
cross apply string_split(replace([address], ',', ' '), ' ') w
left join
(
select value
from string_split ('Road Street', ' ')
) k on w.value = k.value
group by p.id, p.name, p.address
answered Nov 15 '18 at 8:35
SquirrelSquirrel
11.9k22128
Hi @Squirrel, it will produce correct results if you add filter criteria " where w.value <> '' " to remove empty space returns of split function
– Eralper
Nov 15 '18 at 8:54
@Eralper. Good point ! thanks
– Squirrel
Nov 15 '18 at 8:59
add a comment |
here i am using string_split() from SQL Server 2017. You can replace with any string split function available. Just do a search
It is not perfect but it works for your sample.
select p.id, p.name, p.address, count(k.value) * 100.0 / count(*) as pecentage
from tbl_pat p
cross apply string_split(replace([address], ',', ' '), ' ') w
left join
(
select value
from string_split ('Road Street', ' ')
) k on w.value = k.value
group by p.id, p.name, p.address
answered Nov 15 '18 at 8:35
SquirrelSquirrel
11.9k22128
here i am using string_split() from SQL Server 2017. You can replace with any string split function available. Just do a search
It is not perfect but it works for your sample.
select p.id, p.name, p.address, count(k.value) * 100.0 / count(*) as pecentage
from tbl_pat p
cross apply string_split(replace([address], ',', ' '), ' ') w
left join
(
select value
from string_split ('Road Street', ' ')
) k on w.value = k.value
group by p.id, p.name, p.address
answered Nov 15 '18 at 8:35
SquirrelSquirrel
11.9k22128
edited Nov 15 '18 at 8:59
answered Nov 15 '18 at 8:35
SquirrelSquirrel
11.9k22128
answered Nov 15 '18 at 8:35
SquirrelSquirrel
11.9k22128
answered Nov 15 '18 at 8:35
SquirrelSquirrel
11.9k22128
11.9k22128
Hi @Squirrel, it will produce correct results if you add filter criteria " where w.value <> '' " to remove empty space returns of split function
– Eralper
Nov 15 '18 at 8:54
@Eralper. Good point ! thanks
– Squirrel
Nov 15 '18 at 8:59
add a comment |
Hi @Squirrel, it will produce correct results if you add filter criteria " where w.value <> '' " to remove empty space returns of split function
– Eralper
Nov 15 '18 at 8:54
@Eralper. Good point ! thanks
– Squirrel
Nov 15 '18 at 8:59
Hi @Squirrel, it will produce correct results if you add filter criteria " where w.value <> '' " to remove empty space returns of split function
– Eralper
Nov 15 '18 at 8:54
Hi @Squirrel, it will produce correct results if you add filter criteria " where w.value <> '' " to remove empty space returns of split function
– Eralper
Nov 15 '18 at 8:54
@Eralper. Good point ! thanks
– Squirrel
Nov 15 '18 at 8:59
@Eralper. Good point ! thanks
– Squirrel
Nov 15 '18 at 8:59
add a comment |
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.
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%2f53315027%2fcalculate-percentage-of-matching-word%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
What's the formula for calculating this percentage?
– gotqn
Nov 15 '18 at 8:20
This will be hard to do accurately without the ability to do full regex searches.
– Tim Biegeleisen
Nov 15 '18 at 8:23