How to search for a pattern of consecutive records?
- Jeff Moden wrote:
naumon765 wrote:
Hi Jeff, Thanks for taking the time to share your thoughts. I wanted to mention that I do not like the formatting of sqlservercental.com code While I understand it can be useful, I prefer a simpler, cleaner presentation which looks nice and attractive visually I appreciate your contributions and insights, and I hope this feedback comes across in the constructive spirit intended. thanks harish
I agree with you a LOT but graphics don't allow people to easily try your code. I don't post code as graphics. Also, when you're writting a post, look at the menu at the top of the place you type... there's an icon menu and on of the selections is {;}Code . That will bring up a code window that has a nice fixed font and allows things like leading spaces. It's not the best format and the betwee-the-line spacing is a little too large for my liking but it does allow for thoughtful indentation, especially if you replace tables with spaces in SSMS.
Here it is translated using AI
/*===========================================
SECTION 1: Create PATTERNS table
===========================================*/
DROP TABLE IF EXISTS PATTERNS;
CREATE TABLE PATTERNS (
PAT VARCHAR(200)
);
INSERT INTO PATTERNS
SELECT 'a[a-g]bd';
-- SELECT * FROM PATTERNS;
/*===========================================
SECTION 2: Create tb table with sample data
===========================================*/
DROP TABLE IF EXISTS tb;
CREATE TABLE tb (
animal VARCHAR(100)
);
INSERT INTO tb (animal)
VALUES
('1cata'), ('2catfb'), ('3catc'), ('4cata'), ('5catc'),
('1doga'), ('2dogc'), ('3dogb'), ('4dogd'), ('5doga'),
('6dogc'), ('7dogc'),
('1panthera'), ('2panthera'), ('3pantherb'), ('4pantherc'),
('5panthera'), ('6pantherc'), ('7pantherb'), ('8panthera'),
('9pantherc'), ('10pantherb'), ('11pantherd');
-- SELECT * FROM tb;
/*===========================================
SECTION 3: Dynamic SQL construction
===========================================*/
DECLARE @sql VARCHAR(MAX) = '';
;WITH cte AS (
SELECT value
FROM PATTERNS
CROSS APPLY STRING_SPLIT(REPLACE(pat, '[', ']'), ']')
),
Numbers AS (
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.objects
),
cte_ok AS (
SELECT DISTINCT
CASE
WHEN LEN(t.value) > 1 AND value NOT LIKE '%-%'
THEN 'animal LIKE ''%' + SUBSTRING(t.value, n, 1) + '%'''
WHEN value LIKE '%-%'
THEN 'animal BETWEEN '''
+ LEFT(value, 1)
+ ''' AND '''
+ RIGHT(value, 1)
+ ''''
ELSE 'animal LIKE ''%' + value + '%'''
END AS col
FROM cte t
JOIN Numbers n
ON n.n <= LEN(t.value)
)
SELECT @sql = ' WHERE ' + STRING_AGG(col, ' OR ')
FROM cte_ok;
/*===========================================
SECTION 4: Final query execution
===========================================*/
SELECT @sql =
'
SELECT ROW_NUMBER() OVER (
PARTITION BY REPLACE(TRANSLATE(animal, ''0123456789'', '' ''), '' '', '''')
ORDER BY REPLACE(TRANSLATE(animal, ''abcdefghijklmnopqrstuvwxyz'', REPLICATE('' '', 26)), '' '', '''') DESC
) AS rn, *
FROM tb ' + @sql;
-- Wrap in CTE and filter for rn = 1
SELECT @sql = ';WITH cte AS (' + @sql + ') SELECT * FROM cte WHERE rn = 1';
EXEC(@sql); -
What're we looking at here? The t-sql is using STRING_AGG and STRING_SPLIT which were not available in SQL Server 2008. Imo this is really a 2 part question which is why I would typically avoid it back in the day. The first part is a pattern parser. Who likes writing one of those? The rules are brackets and ranges? Idk. If modern t-sql features are available then it seems simplest to use LAG dynamically based on the output of the pattern parser
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
SQL SERVER 2008 = compatible
/*===========================================
SECTION 1: Create PATTERNS table
===========================================*/
IF OBJECT_ID('PATTERNS') IS NOT NULL DROP TABLE PATTERNS;
CREATE TABLE PATTERNS (
PAT VARCHAR(200)
);
INSERT INTO PATTERNS
SELECT 'a[a-g]bd';
/*===========================================
SECTION 2: Create tb table with sample data
===========================================*/
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb;
CREATE TABLE tb (
animal VARCHAR(100)
);
INSERT INTO tb (animal)
VALUES
('1cata'), ('2catfb'), ('3catc'), ('4cata'), ('5catc'),
('1doga'), ('2dogc'), ('3dogb'), ('4dogd'), ('5doga'),
('6dogc'), ('7dogc'),
('1panthera'), ('2panthera'), ('3pantherb'), ('4pantherc'),
('5panthera'), ('6pantherc'), ('7pantherb'), ('8panthera'),
('9pantherc'), ('10pantherb'), ('11pantherd');
/*===========================================
SECTION 3: Dynamic SQL construction
===========================================*/
DECLARE @sql VARCHAR(MAX) = '';
;WITH Numbers AS (
SELECT TOP 200 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.objects
),
cte AS (
SELECT SUBSTRING(pat, n, 1) AS value
FROM PATTERNS
CROSS JOIN Numbers
WHERE n <= LEN(pat)
),
cte_ok AS (
SELECT DISTINCT
CASE
WHEN value LIKE '[a-z]' THEN 'animal LIKE ''%' + value + '%'''
ELSE 'animal LIKE ''%' + value + '%'''
END AS col
FROM cte
)
-- Concatenate using FOR XML PATH
SELECT @sql = ' WHERE ' + STUFF((
SELECT ' OR ' + col
FROM cte_ok
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
,1,4,'');
/*===========================================
SECTION 4: Final query execution
===========================================*/
SET @sql =
'
SELECT ROW_NUMBER() OVER (
PARTITION BY REPLACE(TRANSLATE(animal, ''0123456789'', '' ''), '' '', '''')
ORDER BY REPLACE(TRANSLATE(animal, ''abcdefghijklmnopqrstuvwxyz'', REPLICATE('' '', 26)), '' '', '''') DESC
) AS rn, *
FROM tb ' + @sql;
-- Wrap in CTE and filter for rn = 1
SET @sql = ';WITH cte AS (' + @sql + ') SELECT * FROM cte WHERE rn = 1';
EXEC(@sql); -
Incorrect again and in the same way. TRANSLATE was introduced in 2017. The code is not being run against the runtime it's suggested to be for. Again local plausibility is being passed off as correctness. Sort of like an LLM. The questions are still interesting tho. The only reason this question was answered the way it was in 2011 is because the LAG function didn't exist until SQL Server 2012. The dynamic pattern parsing and string building could be done in various ways to generate a query something like
;WITH cte AS (
SELECT *,
LAG(grade,3) OVER (PARTITION BY animal ORDER BY number) AS g1,
LAG(grade,2) OVER (PARTITION BY animal ORDER BY number) AS g2,
LAG(grade,1) OVER (PARTITION BY animal ORDER BY number) AS g3
FROM #tbl1
)
SELECT number, animal, grade
FROM cte
WHERE g1 = 'a'
AND g2 BETWEEN 'a' AND 'c' COLLATE Latin1_General_BIN2
AND g3 = 'b'
AND grade = 'd';For ranges add COLLATE BIN2 so BETWEEN uses code point order (which is case sensitive)
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
hi Jonathan
powershell python used ( very easy and simple = powerful regex both python powershell )
powershell ... data store , get max per group of animals , return "max per group of animals" containing any letters in abcdefghisx
python .. pattern 'a[a-g]s[x-x]dhi' expanded to unique letters abcdefghisx
final output
wink .. grinning
-
hi Steve
point A to point B
"quickest" "least resource intensive .. in .. [ human efforts ] " "output accurate and consistent"
how you do it ?? your choice LLM BLM or SSM or manually ...... LLMs were created for that purpose
other point being LLM does not work .. google destroyed .. plan B ready ..
how to use LLM ( another PHD topic )
Viewing 6 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply