August 15, 2020 at 2:20 pm
BOR15K wrote:Thank you All!
Steve, apologies - I had no intention to offend you. I have looked into fnTally / the link you have kindly provided.
Much obliged.
Thank you for the kind words! I think I slightly misunderstood the requirements as well. Jason's code made it clear. Please keep at it with the questions if there's anything additional. You said you wanted to extend it yourself and I respect that 🙂
But so... it's not over yet! Or I hope it's not. What would lead you to investigate a set based solution to a problem of this nature? Apparently, Jeff has some code he references " both will produce similar execution plans..." but besides mine I only see one set of code. Also, I have an alternate approach I'm trying as well and we'll see. If it works then I want a speed test against these guys.
Steve - I appreciate the kind words. I was just working off of my own best guess as to the requirements and it's entirely possible that I've missed the mark myself.
I agree with the "I hope it's not over" sentiment. This is an interesting problem and I'll be very interested to see the solutions others come up with, including your "alternate approach" and whatever Jeff has cooked up.
August 15, 2020 at 3:42 pm
BOR15K wrote:I need some basic help, which I will then try to extend, please.
Since very few people ever come back to tell us, I'll make you a deal... Tell us what the extended use is for and I'll show you the solution I just wrote that might already be extended to do what you ultimately need it for. 😉
Well, that will sound very dull, trust me! My kid took CyberFirst Advanced course and they had to do some decryption... To make the long story short, they eventually had to process strings of a fixed length, separated by comma and to compare to another set of strings of a different (fixed) length, separated by coma as well and then to find out which of the strings have similar letters in places X, Y and Z. I was sure I can do it quickly in SQL.... Apparently I could not! Hence I have raised the question here, as I find it very interesting exercise.
August 15, 2020 at 5:37 pm
Ok here's contender #1. This one doesn't split the six and five letter input strings into separate words. It splits it straight into letters and finds the commas and bases everything off of the location of the commas. It uses CROSS JOIN to include the match positions and create the match code. Also, it would be better to use a binary collation if possible. The list was defined as nvarchar(max) but is that the required type?
EDIT: I'm going to keep updating this. It could be simpler
DECLARE @six_letters NVARCHAR(MAX) =
', hurrah, buzzed, Bertha, mettle, holler, collie, yuppie, rabble, tallow, brooks, tassel, meddle, callus, peddle, sobbed, kneels, getter, brooms, dulled, maggot, webbed'
--COLLATE Latin1_General_BIN;
DECLARE @five_letters NVARCHAR(MAX) =
' floor, beTtY, carry, trees, sleep, tells'
--COLLATE Latin1_General_BIN;
declare @search_positions varchar(12) = '1,2,4';
;with
sub_six_cte(split_letter, rn) as (
select substring(@six_letters, t.n, 1), row_number() over (order by t.n) rn
from dbo.fnTally(1, len(@six_letters)) t),
sub_five_cte(split_letter, rn) as (
select substring(@five_letters, t.n, 1), row_number() over (order by t.n) rn
from dbo.fnTally(1, len(@five_letters)) t),
positions_cte(position) as (select try_convert(int, sp.value) from string_split(@search_positions, ',') sp),
six_match_cte(rn, match_code) as (
select
sc.rn,
string_agg(substring(@six_letters, sc.rn+pc.position+1, 1), '') within group (order by pc.position)
from
sub_six_cte sc
cross apply
positions_cte pc
where split_letter=','
group by
sc.rn),
five_match_cte(rn, match_code) as (
select
sc.rn,
string_agg(substring(@five_letters, sc.rn+pc.position+1, 1), '') within group (order by pc.position)
from
sub_five_cte sc
cross apply
positions_cte pc
where split_letter=','
group by
sc.rn),
join_match_cte(rn6, mc6, rn5, mc5) as (
select smc.rn, smc.match_code,
fmc.rn, fmc.match_code
from six_match_cte smc join five_match_cte fmc on smc.match_code=fmc.match_code)
select substring(@six_letters, rn6+2, 6) from join_match_cte
union
select substring(@five_letters, rn5+2, 5) from join_match_cte;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 15, 2020 at 7:14 pm
Just for the fun of it, I decided to change it up so that will identify all of the position/character matches and tell you what the positions are and the matched characters...
DECLARE
@array_6 varchar(MAX) = 'hurrah, buzzed, Bertha, patter, mettle, holler, collie, yuppie, rabble, tallow, brooks, tassel, meddle, callus, peddle, sobbed, kneels, getter, brooms, dulled, maggot, webbed',
@array_5 varchar(MAX) = ' floor, beTtY, carry, trees, sleep, tells',
@min_match_num int = 2;
WITH
cte_a6 AS (
SELECT
val_6 = CONVERT(char(6), TRIM(ss6.value)),
t6.n,
lp6.letter_in_pos
FROM
STRING_SPLIT(@array_6, ',') ss6
CROSS APPLY (VALUES (1),(2),(3),(4),(5),(6) ) t6 (n)
CROSS APPLY ( VALUES (SUBSTRING(TRIM(ss6.value), t6.n, 1)) ) lp6 (letter_in_pos)
),
cte_a5 AS (
SELECT
val_5 = CONVERT(char(5), TRIM(ss5.value)),
t5.n,
lp5.letter_in_pos
FROM
STRING_SPLIT(@array_5, ',') ss5
CROSS APPLY (VALUES (1),(2),(3),(4),(5) ) t5 (n)
CROSS APPLY ( VALUES (SUBSTRING(TRIM(ss5.value), t5.n, 1)) ) lp5 (letter_in_pos)
)
SELECT
a6.val_6,
a5.val_5,
char_pos = CONVERT(varchar(20), STRING_AGG(a6.n, ',') WITHIN GROUP (ORDER BY a6.n)),
match_chars = CONVERT(varchar(20), STRING_AGG(a6.letter_in_pos, '') WITHIN GROUP (ORDER BY a6.n))
FROM
cte_a6 a6
JOIN cte_a5 a5
ON a6.n = a5.n
AND a6.letter_in_pos = a5.letter_in_pos
GROUP BY
a6.val_6,
a5.val_5
HAVING
COUNT(1) >= @min_match_num
ORDER BY
COUNT(1) DESC,
char_pos ASC;
GO
The results...
val_6 val_5 char_pos match_chars
------ ----- -------------------- --------------------
Bertha beTtY 1,2,4 Bet
tallow tells 1,3,4 tll
getter beTtY 2,3,4 ett
mettle beTtY 2,3,4 ett
callus carry 1,2 ca
hurrah carry 3,4 rr
brooks floor 3,4 oo
brooms floor 3,4 oo
kneels sleep 3,4 ee
callus tells 3,4 ll
collie tells 3,4 ll
dulled tells 3,4 ll
holler tells 3,4 ll
patter beTtY 3,4 tt
kneels trees 3,4 ee
August 15, 2020 at 7:33 pm
Post deleted... I didn't see the OP's previous post that had the answer I was looking for.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2020 at 7:41 pm
Deleted due to Jeff's delete. 😀
August 15, 2020 at 7:46 pm
Jeff Moden wrote:BOR15K wrote:I need some basic help, which I will then try to extend, please.
Since very few people ever come back to tell us, I'll make you a deal... Tell us what the extended use is for and I'll show you the solution I just wrote that might already be extended to do what you ultimately need it for. 😉
Well, that will sound very dull, trust me! My kid took CyberFirst Advanced course and they had to do some decryption... To make the long story short, they eventually had to process strings of a fixed length, separated by comma and to compare to another set of strings of a different (fixed) length, separated by coma as well and then to find out which of the strings have similar letters in places X, Y and Z. I was sure I can do it quickly in SQL.... Apparently I could not! Hence I have raised the question here, as I find it very interesting exercise.
Definitely not dull. It IS an interesting problem and a great question. One of the things I had to do in a previous life was work with some software that would try to figure out if the same subject in the text was being covered in some fashion so I have a real life appreciation for this type of problem.
Here's the code I've been talking about. As I told Jason, it's quite similar to what he originally did but doesn't use the fnTally function. I also did it with reuse, possible extended functionality, and ease of use in mind. I may actually have an application for it myself.
Details are in the comments and I used your original example strings and search criteria as a working example in the comments of the code below.
DROP FUNCTION IF EXISTS MatchStringPositions
GO
CREATE OR ALTER FUNCTION dbo.MatchStringPositions
/**********************************************************************************************************************
Purpose:
Given a "left" and "right" string (list) of delimited "words" and the single character delimiter the words are
separated by, return the words from both strings that "match" at the character positions listed in the @Posit
parameter, which must be a comma delimited list.
=======================================================================================================================
Usage:
--===== Basic syntax
SELECT * FROM dbo.MatchStringPositions(@LeftString, @RightString, @Delimiter, @Posit)
;
-----------------------------------------------------------------------------------------------------------------------
--===== Working Example ("Sloshing \" used to wrap the text).
-- "Berta" in the left string MatchOnes "Betty" in the right string on characters 1, 2, and 4.
DECLARE @SomeString1 NVARCHAR(MAX) = 'hurrah, buzzed, Bertha, patter, mettle, holler, collie, yuppie, rabble, tallow\
, brooks, tassel, meddle, callus, peddle, sobbed, kneels, getter, brooms, dulled, maggot, webbed'
,@SomeString2 NVARCHAR(MAX) = ' floor, beTtY, carry, trees, sleep, tells'
,@Posit VARCHAR(30) = '1,2,4' --Try using '3,4' to see how it handles multiple MatchOnes per word.
;
SELECT *
FROM dbo.MatchStringPositions(@SomeString1,@SomeString2,',',@Posit)
;
=======================================================================================================================
Minimum Version/Special Requirements:
1. SQL Server 2017
=======================================================================================================================
References:
Initial Problem:
https://www.sqlservercentral.com/forums/topic/how-to-compare-strings-by-letters-in-certain-places#post-3780650
=======================================================================================================================
Revision History:
Rev 00 - 14 Aug 2020 - Jeff Moden
- Initial Creation and unit test
**********************************************************************************************************************/
(
@LeftString NVARCHAR(MAX)
,@RightString NVARCHAR(MAX)
,@Delimiter NCHAR(1)
,@PositList VARCHAR(100)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
ctePosit AS
(--==== Split the string containing the character positions to MatchOn
SELECT Posit = CONVERT(INT,value)
FROM STRING_SPLIT(@PositList,',')
)
,cteLeftSplit AS
(--===== Split, trim, and case the words from the left string and aggregate the MatchOn search term.
SELECT Word = TRIM(s.value)
,MatchOn = STRING_AGG(SUBSTRING(TRIM(s.value),p.Posit,1),'') WITHIN GROUP (ORDER BY p.Posit)
FROM STRING_SPLIT(UPPER(@LeftString),@Delimiter) s
CROSS APPLY ctePosit p
GROUP BY TRIM(s.value)
)
,cteRightSplit AS
(--===== Split, trim, and case the words from the right string and aggregate the MatchOn search term.
SELECT Word = TRIM(s.value)
,MatchOn = STRING_AGG(SUBSTRING(TRIM(s.value),p.Posit,1),'') WITHIN GROUP (ORDER BY p.Posit)
FROM STRING_SPLIT(UPPER(@RightString),@Delimiter) s
CROSS APPLY ctePosit p
GROUP BY TRIM(s.value)
)--==== Now it's easy to compare the words based on the "MatchOn" column.
SELECT LeftWord = ls.Word
,RightWord = rs.Word
,MatchOn = ls.MatchOn
,PositList = @PositList
FROM cteLeftSplit ls
JOIN cteRightSplit rs
ON ls.MatchOn COLLATE Latin1_General_BIN = rs.MatchOn COLLATE Latin1_General_BIN
;
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2020 at 7:51 pm
Thank you, Jeff!
August 15, 2020 at 8:00 pm
Nice work Jeff. I think my 2nd attempt is actually closer to your solution than the first. We both omitted the tally function/table and simply used the position list to parse the split values directly.
I will try dropping my code into an iTVF and see if there are any major performance differences.
August 15, 2020 at 8:12 pm
Jeff Moden wrote:Heh... it's amazing how similar that is to the code I wrote (still holding out on that because I really am curious what the end game is for this very interesting problem). We even used similar column names. You used "Match_On" and I used "MatchOn".
One difference is that I don't have a limit on the word widths and I don't use a numeric sequence. I also used a single CTE for the "positions" whereas you used one in each WHERE clause... both will produce similar execution plans though because a CTE is re-executed every time it's called.
Jeff - Considering the fact that that I'm consistently impressed by the solutions that you come up with, I'm forced to take that as a compliment. 😀 I hope you decide to post your solution no matter how the OP responds. I always seem to find some new nugget of gold in your solutions.
Now I'm the one that's humbled by a compliment especially considering what I think of your consistently good posts.
Anyway, your comment regarding the limit on word length got me thinking... I initially did it that way because the OP was pretty clear about the word lengths in the criteria so I figured that would be a safe move to make at the time. That said, your comment did shake something loose and it occurred to me that I didn't need the inline tally at all!!!
It, in fact, occurred to me that using a sequential tally to produce rows for every character and then filtering it later with another list of numbers was just plain stupid! Why not just use the list of numbers all by itself?
It eliminates the word length limits, the unneeded tally rows, the tally itself and the WHERE clause... and it makes for a cleaner bit of code and faster compile times.
Heh... that was exactly the same revelation I had (and, I even called myself "stupid", as well) when I first set out to write my version of the code to solve the problem and thought I'd mention it as a possibility.
On the word lengths in the original post... I've simply been burned too many times both on forums and in real life by the fact that people provide an example and forget to tell me that it needs to be "flexible". Unless there's a very particular reason for precisely following only the requirements presented, I just add the flexibility in so that when they come back with the additional requirements, I can tell them, "Try it... it should work as it is" and not have to worry about modifying code. I don't try to make the code "solve world hunger" but I try to make it so clarifying requests don't interfere with dinner time at the end of the day. 😀
I also love this community... you and Steve are still "playing" with different tangents of the original problem and that's how we all learn. I'm particularly interested in your latest rendition of the code because, one, it's a wicked interesting tangent and, two, I might have an even greater use for that tangent. This is fun and good stuff is coming out of it so thank you both!
And thanks to the OP for posting an interesting problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2020 at 3:33 am
Thank you, Jeff!
You bet. Thank you for the feedback on what this is going to be used for.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2020 at 4:35 am
Just for the fun of it, I decided to change it up so that will identify all of the position/character matches and tell you what the positions are and the matched characters...
DECLARE
@array_6 varchar(MAX) = 'hurrah, buzzed, Bertha, patter, mettle, holler, collie, yuppie, rabble, tallow, brooks, tassel, meddle, callus, peddle, sobbed, kneels, getter, brooms, dulled, maggot, webbed',
@array_5 varchar(MAX) = ' floor, beTtY, carry, trees, sleep, tells',
@min_match_num int = 2;
WITH
cte_a6 AS (
SELECT
val_6 = CONVERT(char(6), TRIM(ss6.value)),
t6.n,
lp6.letter_in_pos
FROM
STRING_SPLIT(@array_6, ',') ss6
CROSS APPLY (VALUES (1),(2),(3),(4),(5),(6) ) t6 (n)
CROSS APPLY ( VALUES (SUBSTRING(TRIM(ss6.value), t6.n, 1)) ) lp6 (letter_in_pos)
),
cte_a5 AS (
SELECT
val_5 = CONVERT(char(5), TRIM(ss5.value)),
t5.n,
lp5.letter_in_pos
FROM
STRING_SPLIT(@array_5, ',') ss5
CROSS APPLY (VALUES (1),(2),(3),(4),(5) ) t5 (n)
CROSS APPLY ( VALUES (SUBSTRING(TRIM(ss5.value), t5.n, 1)) ) lp5 (letter_in_pos)
)
SELECT
a6.val_6,
a5.val_5,
char_pos = CONVERT(varchar(20), STRING_AGG(a6.n, ',') WITHIN GROUP (ORDER BY a6.n)),
match_chars = CONVERT(varchar(20), STRING_AGG(a6.letter_in_pos, '') WITHIN GROUP (ORDER BY a6.n))
FROM
cte_a6 a6
JOIN cte_a5 a5
ON a6.n = a5.n
AND a6.letter_in_pos = a5.letter_in_pos
GROUP BY
a6.val_6,
a5.val_5
HAVING
COUNT(1) >= @min_match_num
ORDER BY
COUNT(1) DESC,
char_pos ASC;
GOThe results...
val_6 val_5 char_pos match_chars
------ ----- -------------------- --------------------
Bertha beTtY 1,2,4 Bet
tallow tells 1,3,4 tll
getter beTtY 2,3,4 ett
mettle beTtY 2,3,4 ett
callus carry 1,2 ca
hurrah carry 3,4 rr
brooks floor 3,4 oo
brooms floor 3,4 oo
kneels sleep 3,4 ee
callus tells 3,4 ll
collie tells 3,4 ll
dulled tells 3,4 ll
holler tells 3,4 ll
patter beTtY 3,4 tt
kneels trees 3,4 ee
I've finally had a chance to look at all that more carefully and, man, that has a VERY interesting execution plan. With a pre-split table, I might be able to do some interesting things as a "fuzzy lookup". It won't be as good as a Havenstein "Distance Check" but this might even lead to that.
Like I said, this is one of the reasons I love this community... so many ideas, so little time.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2020 at 1:36 pm
This link takes you to a page that lists the top 1000 most popular baby names.
Here are 5 groups of 50 names (of variable lengths) declared as NVARCHAR(MAX)
declare
@fifty_names_group1 nvarchar(max)=N'Liam, Noah, William, James, Oliver, Benjamin, Elijah, Lucas, Mason, Logan, Alexander, Ethan, Jacob, Michael, Daniel, Henry, Jackson, Sebastian, Aiden, Matthew, Samuel, David, Joseph, Carter, Owen, Wyatt, John, Jack, Luke, Jayden, Dylan, Grayson, Levi, Isaac, Gabriel, Julian, Mateo, Anthony, Jaxon, Lincoln, Joshua, Christopher, Andrew, Theodore, Caleb, Ryan, Asher, Nathan, Thomas, Leo',
@fifty_names_group2 nvarchar(max)=N'Isaiah, Charles, Josiah, Hudson, Christian, Hunter, Connor, Eli, Ezra, Aaron, Landon, Adrian, Jonathan, Nolan, Jeremiah, Easton, Elias, Colton, Cameron, Carson, Robert, Angel, Maverick, Nicholas, Dominic, Jaxson, Greyson, Adam, Ian, Austin, Santiago, Jordan, Cooper, Brayden, Roman, Evan, Ezekiel, Xavier, Jose, Jace, Jameson, Leonardo, Bryson, Axel, Everett, Parker, Kayden, Miles, Sawyer, Jason',
@fifty_names_group3 nvarchar(max)=N'Declan, Weston, Micah, Ayden, Wesley, Luca, Vincent, Damian, Zachary, Silas, Gavin, Chase, Kai, Emmett, Harrison, Nathaniel, Kingston, Cole, Tyler, Bennett, Bentley, Ryker, Tristan, Brandon, Kevin, Luis, George, Ashton, Rowan, Braxton, Ryder, Gael, Ivan, Diego, Maxwell, Max, Carlos, Kaiden, Juan, Maddox, Justin, Waylon, Calvin, Giovanni, Jonah, Abel, Jayce, Jesus, Amir, King',
@fifty_names_group4 nvarchar(max)=N'Beau, Camden, Alex, Jasper, Malachi, Brody, Jude, Blake, Emmanuel, Eric, Brooks, Elliot, Antonio, Abraham, Timothy, Finn, Rhett, Elliott, Edward, August, Xander, Alan, Dean, Lorenzo, Bryce, Karter, Victor, Milo, Miguel, Hayden, Graham, Grant, Zion, Tucker, Jesse, Zayden, Joel, Richard, Patrick, Emiliano, Avery, Nicolas, Brantley, Dawson, Myles, Matteo, River, Steven, Thiago, Zane',
@fifty_names_group5 nvarchar(max)=N'Matias, Judah, Messiah, Jeremy, Preston, Oscar, Kaleb, Alejandro, Marcus, Mark, Peter, Maximus, Barrett, Jax, Andres, Holden, Legend, Charlie, Knox, Kaden, Paxton, Kyrie, Kyle, Griffin, Josue, Kenneth, Beckett, Enzo, Adriel, Arthur, Felix, Bryan, Lukas, Paul, Brian, Colt, Caden, Leon, Archer, Omar, Israel, Aidan, Theo, Javier, Remington, Jaden, Bradley, Emilio, Colin, Riley';
Here are 5 groups of 100 names (of variable lengths) declared as NVARCHAR(MAX)
declare
@hundred_names_group1 nvarchar(max)=N'Liam, Noah, William, James, Oliver, Benjamin, Elijah, Lucas, Mason, Logan, Alexander, Ethan, Jacob, Michael, Daniel, Henry, Jackson, Sebastian, Aiden, Matthew, Samuel, David, Joseph, Carter, Owen, Wyatt, John, Jack, Luke, Jayden, Dylan, Grayson, Levi, Isaac, Gabriel, Julian, Mateo, Anthony, Jaxon, Lincoln, Joshua, Christopher, Andrew, Theodore, Caleb, Ryan, Asher, Nathan, Thomas, Leo, Isaiah, Charles, Josiah, Hudson, Christian, Hunter, Connor, Eli, Ezra, Aaron, Landon, Adrian, Jonathan, Nolan, Jeremiah, Easton, Elias, Colton, Cameron, Carson, Robert, Angel, Maverick, Nicholas, Dominic, Jaxson, Greyson, Adam, Ian, Austin, Santiago, Jordan, Cooper, Brayden, Roman, Evan, Ezekiel, Xavier, Jose, Jace, Jameson, Leonardo, Bryson, Axel, Everett, Parker, Kayden, Miles, Sawyer, Jason',
@hundred_names_group2 nvarchar(max)=N'Declan, Weston, Micah, Ayden, Wesley, Luca, Vincent, Damian, Zachary, Silas, Gavin, Chase, Kai, Emmett, Harrison, Nathaniel, Kingston, Cole, Tyler, Bennett, Bentley, Ryker, Tristan, Brandon, Kevin, Luis, George, Ashton, Rowan, Braxton, Ryder, Gael, Ivan, Diego, Maxwell, Max, Carlos, Kaiden, Juan, Maddox, Justin, Waylon, Calvin, Giovanni, Jonah, Abel, Jayce, Jesus, Amir, King, Beau, Camden, Alex, Jasper, Malachi, Brody, Jude, Blake, Emmanuel, Eric, Brooks, Elliot, Antonio, Abraham, Timothy, Finn, Rhett, Elliott, Edward, August, Xander, Alan, Dean, Lorenzo, Bryce, Karter, Victor, Milo, Miguel, Hayden, Graham, Grant, Zion, Tucker, Jesse, Zayden, Joel, Richard, Patrick, Emiliano, Avery, Nicolas, Brantley, Dawson, Myles, Matteo, River, Steven, Thiago, Zane',
@hundred_names_group3 nvarchar(max)=N'Matias, Judah, Messiah, Jeremy, Preston, Oscar, Kaleb, Alejandro, Marcus, Mark, Peter, Maximus, Barrett, Jax, Andres, Holden, Legend, Charlie, Knox, Kaden, Paxton, Kyrie, Kyle, Griffin, Josue, Kenneth, Beckett, Enzo, Adriel, Arthur, Felix, Bryan, Lukas, Paul, Brian, Colt, Caden, Leon, Archer, Omar, Israel, Aidan, Theo, Javier, Remington, Jaden, Bradley, Emilio, Colin, Riley, Cayden, Phoenix, Clayton, Simon, Ace, Nash, Derek, Rafael, Zander, Brady, Jorge, Jake, Louis, Damien, Karson, Walker, Maximiliano, Amari, Sean, Chance, Walter, Martin, Finley, Andre, Tobias, Cash, Corbin, Arlo, Iker, Erick, Emerson, Gunner, Cody, Stephen, Francisco, Killian, Dallas, Reid, Manuel, Lane, Atlas, Rylan, Jensen, Ronan, Beckham, Daxton, Anderson, Kameron, Raymond, Orion',
@hundred_names_group4 nvarchar(max)=N'Cristian, Tanner, Kyler, Jett, Cohen, Ricardo, Spencer, Gideon, Ali, Fernando, Jaiden, Titus, Travis, Bodhi, Eduardo, Dante, Ellis, Prince, Kane, Luka, Kash, Hendrix, Desmond, Donovan, Mario, Atticus, Cruz, Garrett, Hector, Angelo, Jeffrey, Edwin, Cesar, Zayn, Devin, Conor, Warren, Odin, Jayceon, Romeo, Julius, Jaylen, Hayes, Kayson, Muhammad, Jaxton, Joaquin, Caiden, Dakota, Major, Keegan, Sergio, Marshall, Johnny, Kade, Edgar, Leonel, Ismael, Marco, Tyson, Wade, Collin, Troy, Nasir, Conner, Adonis, Jared, Rory, Andy, Jase, Lennox, Shane, Malik, Ari, Reed, Seth, Clark, Erik, Lawson, Trevor, Gage, Nico, Malakai, Quinn, Cade, Johnathan, Sullivan, Solomon, Cyrus, Fabian, Pedro, Frank, Shawn, Malcolm, Khalil, Nehemiah, Dalton, Mathias, Jay, Ibrahim',
@hundred_names_group5 nvarchar(max)=N'Peyton, Winston, Kason, Zayne, Noel, Princeton, Matthias, Gregory, Sterling, Dominick, Elian, Grady, Russell, Finnegan, Ruben, Gianni, Porter, Kendrick, Leland, Pablo, Allen, Hugo, Raiden, Kolton, Remy, Ezequiel, Damon, Emanuel, Zaiden, Otto, Bowen, Marcos, Abram, Kasen, Franklin, Royce, Jonas, Sage, Philip, Esteban, Drake, Kashton, Roberto, Harvey, Alexis, Kian, Jamison, Maximilian, Adan, Milan, Phillip, Albert, Dax, Mohamed, Ronin, Kamden, Hank, Memphis, Oakley, Augustus, Drew, Moises, Armani, Rhys, Benson, Jayson, Kyson, Braylen, Corey, Gunnar, Omari, Alonzo, Landen, Armando, Derrick, Dexter, Enrique, Bruce, Nikolai, Francis, Rocco, Kairo, Royal, Zachariah, Arjun, Deacon, Skyler, Eden, Alijah, Rowen, Pierce, Uriel, Ronald, Luciano, Tate, Frederick, Kieran, Lawrence, Moses, Rodrigo';
Here are 2 groups of 500 names (of variable lengths) declared as NVARCHAR(MAX)
declare
@five_hundred_names_group1 nvarchar(max)=N'Liam, Noah, William, James, Oliver, Benjamin, Elijah, Lucas, Mason, Logan, Alexander, Ethan, Jacob, Michael, Daniel, Henry, Jackson, Sebastian, Aiden, Matthew, Samuel, David, Joseph, Carter, Owen, Wyatt, John, Jack, Luke, Jayden, Dylan, Grayson, Levi, Isaac, Gabriel, Julian, Mateo, Anthony, Jaxon, Lincoln, Joshua, Christopher, Andrew, Theodore, Caleb, Ryan, Asher, Nathan, Thomas, Leo, Isaiah, Charles, Josiah, Hudson, Christian, Hunter, Connor, Eli, Ezra, Aaron, Landon, Adrian, Jonathan, Nolan, Jeremiah, Easton, Elias, Colton, Cameron, Carson, Robert, Angel, Maverick, Nicholas, Dominic, Jaxson, Greyson, Adam, Ian, Austin, Santiago, Jordan, Cooper, Brayden, Roman, Evan, Ezekiel, Xavier, Jose, Jace, Jameson, Leonardo, Bryson, Axel, Everett, Parker, Kayden, Miles, Sawyer, Jason, Declan, Weston, Micah, Ayden, Wesley, Luca, Vincent, Damian, Zachary, Silas, Gavin, Chase, Kai, Emmett, Harrison, Nathaniel, Kingston, Cole, Tyler, Bennett, Bentley, Ryker, Tristan, Brandon, Kevin, Luis, George, Ashton, Rowan, Braxton, Ryder, Gael, Ivan, Diego, Maxwell, Max, Carlos, Kaiden, Juan, Maddox, Justin, Waylon, Calvin, Giovanni, Jonah, Abel, Jayce, Jesus, Amir, King, Beau, Camden, Alex, Jasper, Malachi, Brody, Jude, Blake, Emmanuel, Eric, Brooks, Elliot, Antonio, Abraham, Timothy, Finn, Rhett, Elliott, Edward, August, Xander, Alan, Dean, Lorenzo, Bryce, Karter, Victor, Milo, Miguel, Hayden, Graham, Grant, Zion, Tucker, Jesse, Zayden, Joel, Richard, Patrick, Emiliano, Avery, Nicolas, Brantley, Dawson, Myles, Matteo, River, Steven, Thiago, Zane, Matias, Judah, Messiah, Jeremy, Preston, Oscar, Kaleb, Alejandro, Marcus, Mark, Peter, Maximus, Barrett, Jax, Andres, Holden, Legend, Charlie, Knox, Kaden, Paxton, Kyrie, Kyle, Griffin, Josue, Kenneth, Beckett, Enzo, Adriel, Arthur, Felix, Bryan, Lukas, Paul, Brian, Colt, Caden, Leon, Archer, Omar, Israel, Aidan, Theo, Javier, Remington, Jaden, Bradley, Emilio, Colin, Riley, Cayden, Phoenix, Clayton, Simon, Ace, Nash, Derek, Rafael, Zander, Brady, Jorge, Jake, Louis, Damien, Karson, Walker, Maximiliano, Amari, Sean, Chance, Walter, Martin, Finley, Andre, Tobias, Cash, Corbin, Arlo, Iker, Erick, Emerson, Gunner, Cody, Stephen, Francisco, Killian, Dallas, Reid, Manuel, Lane, Atlas, Rylan, Jensen, Ronan, Beckham, Daxton, Anderson, Kameron, Raymond, Orion, Cristian, Tanner, Kyler, Jett, Cohen, Ricardo, Spencer, Gideon, Ali, Fernando, Jaiden, Titus, Travis, Bodhi, Eduardo, Dante, Ellis, Prince, Kane, Luka, Kash, Hendrix, Desmond, Donovan, Mario, Atticus, Cruz, Garrett, Hector, Angelo, Jeffrey, Edwin, Cesar, Zayn, Devin, Conor, Warren, Odin, Jayceon, Romeo, Julius, Jaylen, Hayes, Kayson, Muhammad, Jaxton, Joaquin, Caiden, Dakota, Major, Keegan, Sergio, Marshall, Johnny, Kade, Edgar, Leonel, Ismael, Marco, Tyson, Wade, Collin, Troy, Nasir, Conner, Adonis, Jared, Rory, Andy, Jase, Lennox, Shane, Malik, Ari, Reed, Seth, Clark, Erik, Lawson, Trevor, Gage, Nico, Malakai, Quinn, Cade, Johnathan, Sullivan, Solomon, Cyrus, Fabian, Pedro, Frank, Shawn, Malcolm, Khalil, Nehemiah, Dalton, Mathias, Jay, Ibrahim, Peyton, Winston, Kason, Zayne, Noel, Princeton, Matthias, Gregory, Sterling, Dominick, Elian, Grady, Russell, Finnegan, Ruben, Gianni, Porter, Kendrick, Leland, Pablo, Allen, Hugo, Raiden, Kolton, Remy, Ezequiel, Damon, Emanuel, Zaiden, Otto, Bowen, Marcos, Abram, Kasen, Franklin, Royce, Jonas, Sage, Philip, Esteban, Drake, Kashton, Roberto, Harvey, Alexis, Kian, Jamison, Maximilian, Adan, Milan, Phillip, Albert, Dax, Mohamed, Ronin, Kamden, Hank, Memphis, Oakley, Augustus, Drew, Moises, Armani, Rhys, Benson, Jayson, Kyson, Braylen, Corey, Gunnar, Omari, Alonzo, Landen, Armando, Derrick, Dexter, Enrique, Bruce, Nikolai, Francis, Rocco, Kairo, Royal, Zachariah, Arjun, Deacon, Skyler, Eden, Alijah, Rowen, Pierce, Uriel, Ronald, Luciano, Tate, Frederick, Kieran, Lawrence, Moses, Rodrigo',
@five_hundred_names_group2 nvarchar(max)=N'Brycen, Leonidas, Nixon, Keith, Chandler, Case, Davis, Asa, Darius, Isaias, Aden, Jaime, Landyn, Raul, Niko, Trenton, Apollo, Cairo, Izaiah, Scott, Dorian, Julio, Wilder, Santino, Dustin, Donald, Raphael, Saul, Taylor, Ayaan, Duke, Ryland, Tatum, Ahmed, Moshe, Edison, Emmitt, Cannon, Alec, Danny, Keaton, Roy, Conrad, Roland, Quentin, Lewis, Samson, Brock, Kylan, Cason, Ahmad, Jalen, Nikolas, Braylon, Kamari, Dennis, Callum, Justice, Soren, Rayan, Aarav, Gerardo, Ares, Brendan, Jamari, Kaison, Yusuf, Issac, Jasiah, Callen, Forrest, Makai, Crew, Kobe, Bo, Julien, Mathew, Braden, Johan, Marvin, Zaid, Stetson, Casey, Ty, Ariel, Tony, Zain, Callan, Cullen, Sincere, Uriah, Dillon, Kannon, Colby, Axton, Cassius, Quinton, Mekhi, Reece, Alessandro, Jerry, Mauricio, Sam, Trey, Mohammad, Alberto, Gustavo, Arturo, Fletcher, Marcelo, Abdiel, Hamza, Alfredo, Chris, Finnley, Curtis, Kellan, Quincy, Kase, Harry, Kyree, Wilson, Cayson, Hezekiah, Kohen, Neil, Mohammed, Raylan, Kaysen, Lucca, Sylas, Mack, Leonard, Lionel, Ford, Roger, Rex, Alden, Boston, Colson, Briggs, Zeke, Dariel, Kingsley, Valentino, Jamir, Salvador, Vihaan, Mitchell, Lance, Lucian, Darren, Jimmy, Alvin, Amos, Tripp, Zaire, Layton, Reese, Casen, Colten, Brennan, Korbin, Sonny, Bruno, Orlando, Devon, Huxley, Boone, Maurice, Nelson, Douglas, Randy, Gary, Lennon, Titan, Denver, Jaziel, Noe, Jefferson, Ricky, Lochlan, Rayden, Bryant, Langston, Lachlan, Clay, Abdullah, Lee, Baylor, Leandro, Ben, Kareem, Layne, Joe, Crosby, Deandre, Demetrius, Kellen, Carl, Jakob, Ridge, Bronson, Jedidiah, Rohan, Larry, Stanley, Tomas, Shiloh, Thaddeus, Watson, Baker, Vicente, Koda, Jagger, Nathanael, Carmelo, Shepherd, Graysen, Melvin, Ernesto, Jamie, Yosef, Clyde, Eddie, Tristen, Grey, Ray, Tommy, Samir, Ramon, Santana, Kristian, Marcel, Wells, Zyaire, Brecken, Byron, Otis, Reyansh, Axl, Joey, Trace, Morgan, Musa, Harlan, Enoch, Henrik, Kristopher, Talon, Rey, Guillermo, Houston, Jon, Vincenzo, Dane, Terry, Azariah, Castiel, Kye, Augustine, Zechariah, Joziah, Kamryn, Hassan, Jamal, Chaim, Bodie, Emery, Branson, Jaxtyn, Kole, Wayne, Aryan, Alonso, Brixton, Madden, Allan, Flynn, Jaxen, Harley, Magnus, Sutton, Dash, Anders, Westley, Brett, Emory, Felipe, Yousef, Jadiel, Mordechai, Dominik, Junior, Eliseo, Fisher, Harold, Jaxxon, Kamdyn, Maximo, Caspian, Kelvin, Damari, Fox, Trent, Hugh, Briar, Franco, Keanu, Terrance, Yahir, Ameer, Kaiser, Thatcher, Ishaan, Koa, Merrick, Coen, Rodney, Brayan, London, Rudy, Gordon, Bobby, Aron, Marc, Van, Anakin, Canaan, Dario, Reginald, Westin, Darian, Ledger, Leighton, Maxton, Tadeo, Valentin, Aldo, Khalid, Nickolas, Toby, Dayton, Jacoby, Billy, Gatlin, Elisha, Jabari, Jermaine, Alvaro, Marlon, Mayson, Blaze, Jeffery, Kace, Braydon, Achilles, Brysen, Saint, Xzavier, Aydin, Eugene, Adrien, Cain, Kylo, Nova, Onyx, Arian, Bjorn, Jerome, Miller, Alfred, Kenzo, Kyng, Leroy, Maison, Jordy, Stefan, Wallace, Benicio, Kendall, Zayd, Blaine, Tristian, Anson, Gannon, Jeremias, Marley, Ronnie, Dangelo, Kody, Will, Bentlee, Gerald, Salvatore, Turner, Chad, Misael, Mustafa, Konnor, Maxim, Rogelio, Zakai, Cory, Judson, Brentley, Darwin, Louie, Ulises, Dakari, Rocky, Wesson, Alfonso, Payton, Dwayne, Juelz, Duncan, Keagan, Deshawn, Bode, Bridger, Skylar, Brodie, Landry, Avi, Keenan, Reuben, Jaxx, Rene, Yehuda, Imran, Yael, Alexzander, Willie, Cristiano, Heath, Lyric, Davion, Elon, Karsyn, Krew, Jairo, Maddux, Ephraim, Ignacio, Vivaan, Aries, Vance, Boden, Lyle, Ralph, Reign, Camilo, Draven, Terrence, Idris, Ira, Javion, Jericho, Khari, Marcellus, Creed, Shepard, Terrell, Ahmir, Camdyn, Cedric, Howard, Jad, Zahir, Harper, Justus, Forest, Gibson, Zev, Alaric, Decker, Ernest, Jesiah, Torin, Benedict, Bowie, Deangelo, Genesis, Harlem, Kalel, Kylen, Bishop, Immanuel, Lian, Zavier, Archie, Davian, Gus, Kabir, Korbyn, Randall, Benton, Coleman, Markus';
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 17, 2020 at 4:25 am
I don't think we need 2 calls to Tally function here.
If join parsed lists of names through Tally then we need only 1 instance of it.
I also use my Tally Generator function which allows limiting the number of returning rows by submitting the max len of the names as a parameter.
declare
@five_hundred_names_group1 nvarchar(max)=N'Liam, Noah, William, James, Oliver, Benjamin, Elijah, Lucas, Mason, Logan, Alexander, Ethan, Jacob, Michael, Daniel, Henry, Jackson, Sebastian, Aiden, Matthew, Samuel, David, Joseph, Carter, Owen, Wyatt, John, Jack, Luke, Jayden, Dylan, Grayson, Levi, Isaac, Gabriel, Julian, Mateo, Anthony, Jaxon, Lincoln, Joshua, Christopher, Andrew, Theodore, Caleb, Ryan, Asher, Nathan, Thomas, Leo, Isaiah, Charles, Josiah, Hudson, Christian, Hunter, Connor, Eli, Ezra, Aaron, Landon, Adrian, Jonathan, Nolan, Jeremiah, Easton, Elias, Colton, Cameron, Carson, Robert, Angel, Maverick, Nicholas, Dominic, Jaxson, Greyson, Adam, Ian, Austin, Santiago, Jordan, Cooper, Brayden, Roman, Evan, Ezekiel, Xavier, Jose, Jace, Jameson, Leonardo, Bryson, Axel, Everett, Parker, Kayden, Miles, Sawyer, Jason, Declan, Weston, Micah, Ayden, Wesley, Luca, Vincent, Damian, Zachary, Silas, Gavin, Chase, Kai, Emmett, Harrison, Nathaniel, Kingston, Cole, Tyler, Bennett, Bentley, Ryker, Tristan, Brandon, Kevin, Luis, George, Ashton, Rowan, Braxton, Ryder, Gael, Ivan, Diego, Maxwell, Max, Carlos, Kaiden, Juan, Maddox, Justin, Waylon, Calvin, Giovanni, Jonah, Abel, Jayce, Jesus, Amir, King, Beau, Camden, Alex, Jasper, Malachi, Brody, Jude, Blake, Emmanuel, Eric, Brooks, Elliot, Antonio, Abraham, Timothy, Finn, Rhett, Elliott, Edward, August, Xander, Alan, Dean, Lorenzo, Bryce, Karter, Victor, Milo, Miguel, Hayden, Graham, Grant, Zion, Tucker, Jesse, Zayden, Joel, Richard, Patrick, Emiliano, Avery, Nicolas, Brantley, Dawson, Myles, Matteo, River, Steven, Thiago, Zane, Matias, Judah, Messiah, Jeremy, Preston, Oscar, Kaleb, Alejandro, Marcus, Mark, Peter, Maximus, Barrett, Jax, Andres, Holden, Legend, Charlie, Knox, Kaden, Paxton, Kyrie, Kyle, Griffin, Josue, Kenneth, Beckett, Enzo, Adriel, Arthur, Felix, Bryan, Lukas, Paul, Brian, Colt, Caden, Leon, Archer, Omar, Israel, Aidan, Theo, Javier, Remington, Jaden, Bradley, Emilio, Colin, Riley, Cayden, Phoenix, Clayton, Simon, Ace, Nash, Derek, Rafael, Zander, Brady, Jorge, Jake, Louis, Damien, Karson, Walker, Maximiliano, Amari, Sean, Chance, Walter, Martin, Finley, Andre, Tobias, Cash, Corbin, Arlo, Iker, Erick, Emerson, Gunner, Cody, Stephen, Francisco, Killian, Dallas, Reid, Manuel, Lane, Atlas, Rylan, Jensen, Ronan, Beckham, Daxton, Anderson, Kameron, Raymond, Orion, Cristian, Tanner, Kyler, Jett, Cohen, Ricardo, Spencer, Gideon, Ali, Fernando, Jaiden, Titus, Travis, Bodhi, Eduardo, Dante, Ellis, Prince, Kane, Luka, Kash, Hendrix, Desmond, Donovan, Mario, Atticus, Cruz, Garrett, Hector, Angelo, Jeffrey, Edwin, Cesar, Zayn, Devin, Conor, Warren, Odin, Jayceon, Romeo, Julius, Jaylen, Hayes, Kayson, Muhammad, Jaxton, Joaquin, Caiden, Dakota, Major, Keegan, Sergio, Marshall, Johnny, Kade, Edgar, Leonel, Ismael, Marco, Tyson, Wade, Collin, Troy, Nasir, Conner, Adonis, Jared, Rory, Andy, Jase, Lennox, Shane, Malik, Ari, Reed, Seth, Clark, Erik, Lawson, Trevor, Gage, Nico, Malakai, Quinn, Cade, Johnathan, Sullivan, Solomon, Cyrus, Fabian, Pedro, Frank, Shawn, Malcolm, Khalil, Nehemiah, Dalton, Mathias, Jay, Ibrahim, Peyton, Winston, Kason, Zayne, Noel, Princeton, Matthias, Gregory, Sterling, Dominick, Elian, Grady, Russell, Finnegan, Ruben, Gianni, Porter, Kendrick, Leland, Pablo, Allen, Hugo, Raiden, Kolton, Remy, Ezequiel, Damon, Emanuel, Zaiden, Otto, Bowen, Marcos, Abram, Kasen, Franklin, Royce, Jonas, Sage, Philip, Esteban, Drake, Kashton, Roberto, Harvey, Alexis, Kian, Jamison, Maximilian, Adan, Milan, Phillip, Albert, Dax, Mohamed, Ronin, Kamden, Hank, Memphis, Oakley, Augustus, Drew, Moises, Armani, Rhys, Benson, Jayson, Kyson, Braylen, Corey, Gunnar, Omari, Alonzo, Landen, Armando, Derrick, Dexter, Enrique, Bruce, Nikolai, Francis, Rocco, Kairo, Royal, Zachariah, Arjun, Deacon, Skyler, Eden, Alijah, Rowen, Pierce, Uriel, Ronald, Luciano, Tate, Frederick, Kieran, Lawrence, Moses, Rodrigo',
@five_hundred_names_group2 nvarchar(max)=N'Brycen, Leonidas, Nixon, Keith, Chandler, Case, Davis, Asa, Darius, Isaias, Aden, Jaime, Landyn, Raul, Niko, Trenton, Apollo, Cairo, Izaiah, Scott, Dorian, Julio, Wilder, Santino, Dustin, Donald, Raphael, Saul, Taylor, Ayaan, Duke, Ryland, Tatum, Ahmed, Moshe, Edison, Emmitt, Cannon, Alec, Danny, Keaton, Roy, Conrad, Roland, Quentin, Lewis, Samson, Brock, Kylan, Cason, Ahmad, Jalen, Nikolas, Braylon, Kamari, Dennis, Callum, Justice, Soren, Rayan, Aarav, Gerardo, Ares, Brendan, Jamari, Kaison, Yusuf, Issac, Jasiah, Callen, Forrest, Makai, Crew, Kobe, Bo, Julien, Mathew, Braden, Johan, Marvin, Zaid, Stetson, Casey, Ty, Ariel, Tony, Zain, Callan, Cullen, Sincere, Uriah, Dillon, Kannon, Colby, Axton, Cassius, Quinton, Mekhi, Reece, Alessandro, Jerry, Mauricio, Sam, Trey, Mohammad, Alberto, Gustavo, Arturo, Fletcher, Marcelo, Abdiel, Hamza, Alfredo, Chris, Finnley, Curtis, Kellan, Quincy, Kase, Harry, Kyree, Wilson, Cayson, Hezekiah, Kohen, Neil, Mohammed, Raylan, Kaysen, Lucca, Sylas, Mack, Leonard, Lionel, Ford, Roger, Rex, Alden, Boston, Colson, Briggs, Zeke, Dariel, Kingsley, Valentino, Jamir, Salvador, Vihaan, Mitchell, Lance, Lucian, Darren, Jimmy, Alvin, Amos, Tripp, Zaire, Layton, Reese, Casen, Colten, Brennan, Korbin, Sonny, Bruno, Orlando, Devon, Huxley, Boone, Maurice, Nelson, Douglas, Randy, Gary, Lennon, Titan, Denver, Jaziel, Noe, Jefferson, Ricky, Lochlan, Rayden, Bryant, Langston, Lachlan, Clay, Abdullah, Lee, Baylor, Leandro, Ben, Kareem, Layne, Joe, Crosby, Deandre, Demetrius, Kellen, Carl, Jakob, Ridge, Bronson, Jedidiah, Rohan, Larry, Stanley, Tomas, Shiloh, Thaddeus, Watson, Baker, Vicente, Koda, Jagger, Nathanael, Carmelo, Shepherd, Graysen, Melvin, Ernesto, Jamie, Yosef, Clyde, Eddie, Tristen, Grey, Ray, Tommy, Samir, Ramon, Santana, Kristian, Marcel, Wells, Zyaire, Brecken, Byron, Otis, Reyansh, Axl, Joey, Trace, Morgan, Musa, Harlan, Enoch, Henrik, Kristopher, Talon, Rey, Guillermo, Houston, Jon, Vincenzo, Dane, Terry, Azariah, Castiel, Kye, Augustine, Zechariah, Joziah, Kamryn, Hassan, Jamal, Chaim, Bodie, Emery, Branson, Jaxtyn, Kole, Wayne, Aryan, Alonso, Brixton, Madden, Allan, Flynn, Jaxen, Harley, Magnus, Sutton, Dash, Anders, Westley, Brett, Emory, Felipe, Yousef, Jadiel, Mordechai, Dominik, Junior, Eliseo, Fisher, Harold, Jaxxon, Kamdyn, Maximo, Caspian, Kelvin, Damari, Fox, Trent, Hugh, Briar, Franco, Keanu, Terrance, Yahir, Ameer, Kaiser, Thatcher, Ishaan, Koa, Merrick, Coen, Rodney, Brayan, London, Rudy, Gordon, Bobby, Aron, Marc, Van, Anakin, Canaan, Dario, Reginald, Westin, Darian, Ledger, Leighton, Maxton, Tadeo, Valentin, Aldo, Khalid, Nickolas, Toby, Dayton, Jacoby, Billy, Gatlin, Elisha, Jabari, Jermaine, Alvaro, Marlon, Mayson, Blaze, Jeffery, Kace, Braydon, Achilles, Brysen, Saint, Xzavier, Aydin, Eugene, Adrien, Cain, Kylo, Nova, Onyx, Arian, Bjorn, Jerome, Miller, Alfred, Kenzo, Kyng, Leroy, Maison, Jordy, Stefan, Wallace, Benicio, Kendall, Zayd, Blaine, Tristian, Anson, Gannon, Jeremias, Marley, Ronnie, Dangelo, Kody, Will, Bentlee, Gerald, Salvatore, Turner, Chad, Misael, Mustafa, Konnor, Maxim, Rogelio, Zakai, Cory, Judson, Brentley, Darwin, Louie, Ulises, Dakari, Rocky, Wesson, Alfonso, Payton, Dwayne, Juelz, Duncan, Keagan, Deshawn, Bode, Bridger, Skylar, Brodie, Landry, Avi, Keenan, Reuben, Jaxx, Rene, Yehuda, Imran, Yael, Alexzander, Willie, Cristiano, Heath, Lyric, Davion, Elon, Karsyn, Krew, Jairo, Maddux, Ephraim, Ignacio, Vivaan, Aries, Vance, Boden, Lyle, Ralph, Reign, Camilo, Draven, Terrence, Idris, Ira, Javion, Jericho, Khari, Marcellus, Creed, Shepard, Terrell, Ahmir, Camdyn, Cedric, Howard, Jad, Zahir, Harper, Justus, Forest, Gibson, Zev, Alaric, Decker, Ernest, Jesiah, Torin, Benedict, Bowie, Deangelo, Genesis, Harlem, Kalel, Kylen, Bishop, Immanuel, Lian, Zavier, Archie, Davian, Gus, Kabir, Korbyn, Randall, Benton, Coleman, Markus';
DECLARE @positionsToMatch varchar(100) = '1,2,4', @MaxLen int;
SELECT @MaxLen = MAX(LEN(Item))
from (
select LTRIM(RTRIM(Item)) Item From [dbo].[SplitQuoted_1D_TVF] (@five_hundred_names_group1, ',', '"')
UNION
select LTRIM(RTRIM(Item)) From [dbo].[SplitQuoted_1D_TVF] (@five_hundred_names_group2, ',', '"')
) T;
raiserror('MaxLen: %d', 0,1, @MaxLen);
WITH PositionsList AS (
SELECT Item Position
from [dbo].[SplitQuoted_1D_TVF] (@positionsToMatch, ',', '"')
)
select W6.ItemNo, W5.ItemNo, LTRIM(RTRIM(W6.Item)) names_group1 , LTRIM(RTRIM(W5.Item)) name_group2
From [dbo].[SplitQuoted_1D_TVF] (@five_hundred_names_group1, ',', '"') W6
CROSS JOIN [dbo].[TallyGenerator] (1, @MaxLen, null, 1) T
INNER JOIN [dbo].[SplitQuoted_1D_TVF] (@five_hundred_names_group2, ',', '"') W5
on SUBSTRING(LTRIM(RTRIM(W6.Item)), N, 1) = SUBSTRING(LTRIM(RTRIM(W5.Item)), N, 1)
WHERE N in (SELECT Position from PositionsList )
GROUP BY W6.ItemNo, LTRIM(RTRIM(W6.Item)), W5.ItemNo, LTRIM(RTRIM(W5.Item))
HAVING COUNT(*) = (SELECT COUNT(*) from PositionsList ) ;
I used here my own splitter which allows to split >8k long strings:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:SF
-- Create date: 2016-09-20
-- Description:Parses a strind of quoted delimited values into columns
-- =============================================
CREATE FUNCTION [dbo].[SplitQuoted_1D_TVF]
(
-- Add the parameters for the function here
@Text nvarchar(max),
@Delimiter NVARCHAR(10),
@Quote NCHAR(1)
)
RETURNS @SplitQuoted_1D TABLE (
ItemNo int IDENTITY(1,1),
Item nvarchar(4000) COLLATE DATABASE_DEFAULT
)
AS BEGIN
DECLARE @QuoteReplacement NCHAR(1), @MaxLen INT
SELECT TOP 1 @QuoteReplacement = Chr
FROM (
SELECT NCHAR(7) UNION SELECT NCHAR(255) UNION SELECT NCHAR(512) UNION SELECT '`'
) C (Chr)
WHERE PATINDEX( '%'+C.Chr + '%', @Text) = 0
IF DATALENGTH(@Text) = 0
RETURN
INSERT INTO @SplitQuoted_1D
( Item )
SELECT --ItemNo,
REPLACE(
SUBSTRING(SBSTR, 1 + QuoteLen,
ISNULL(NULLIF(
CHARINDEX( CASE WHEN QuoteLen>0 THEN @Quote ELSE @Delimiter END ,
REPLACE (
substring(SBSTR, 1+QuoteLen, 4000),
@Quote + @Quote, @QuoteReplacement + @QuoteReplacement)
) ,0) - 1 -- Length of remaining SBSTR = position of ending character - 1
-- if quote/delimiter is not found then we take the rest of SBSTR
,4000) )
,@Quote + @Quote, @Quote)
Item
FROM (
SELECT TN.N BOL,
DATALENGTH(@Quote)/2 * CASE WHEN SUBSTRING(@Text, TN.N, DATALENGTH(@Quote)/2) = @Quote THEN 1 ELSE 0 END QuoteLen,
SUBSTRING (@Text, TN.N, 4000) SBSTR
FROM dbo.TallyGenerator(1, DATALENGTH(@Text)/2, NULL, 1) TN
WHERE (TN.N = 1 OR SUBSTRING(@Text, TN.N-DATALENGTH(@Delimiter)/2, DATALENGTH(@Delimiter)/2) = @Delimiter )
-- Here we check that number of quotes before the current delimiter is even
AND (SELECT COUNT(*) FROM dbo.TallyGenerator(1, TN.N, NULL, DATALENGTH(@Quote)/2) te
--we take the string from 1st character to the CURRENT character and count all occasions when PREVIOUS character is a quote.
WHERE SUBSTRING(@Text, te.N-DATALENGTH(@Quote)/2, DATALENGTH(@Quote)/2) = @Quote
) % 2 = 0
) T1
ORDER BY BOL
RETURN
END
GO
_____________
Code for TallyGenerator
August 18, 2020 at 11:54 am
The 1,000 names in the test variables vary in length from 2 to 11 characters. Here's some code to generate 8 random tests and store them in a UDT. I figure we can pass 2 name lists (VARCHAR(MAX)) and 1 random test UDT to stored procedures as parameters.
2x Single integer tests
2x Two integer tests
4x three integer tests
/* user defined type to store random test */
drop type if exists dbo.MatchTests;
go
create type dbo.MatchTests as table(
positions varchar(20) not null);
go
declare
@Test_MatchTests dbo.MatchTests;
;with
position_cte as (
select top 1 position from (values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) p(position)
order by newid())
insert @Test_MatchTests(positions)
select (select convert(varchar, position) from position_cte)
union all
select (select convert(varchar, position) from position_cte)
union all
select concat_ws(',',(select position from position_cte),(select position from position_cte))
union all
select concat_ws(',',(select position from position_cte),(select position from position_cte))
union all
select concat_ws(',',(select position from position_cte),(select position from position_cte), (select position from position_cte))
union all
select concat_ws(',',(select position from position_cte),(select position from position_cte), (select position from position_cte))
union all
select concat_ws(',',(select position from position_cte),(select position from position_cte), (select position from position_cte))
union all
select concat_ws(',',(select position from position_cte),(select position from position_cte), (select position from position_cte));
select * from @Test_MatchTests;
Results
positions
4
3
9,7
5,3
8,5,3
5,9,3
5,2,5
4,9,6
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply