August 30, 2011 at 9:09 am
peanuts91 (8/30/2011)
Thanks Gianluca.
But If I have a field containing for example 'Abraham', it will detect the A appears 3 times. But not 3 times in a row like aaa.
Try this one:
;with data (string) as (
SELECT 'Saaaateres'
UNION ALL
SELECT 'NoRepeats'
UNION ALL
SELECT 'aabbbcdef'
UNION ALL
SELECT 'Abraham'
),
tenRows (N) AS (
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
),
tally (N) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM tenRows A
CROSS JOIN tenRows B
CROSS JOIN tenRows C
CROSS JOIN tenRows D
CROSS JOIN tenRows E
CROSS JOIN tenRows F
)
SELECT *
FROM data AS D
CROSS APPLY (
SELECT chr
FROM (
SELECT DISTINCT SUBSTRING(D.string,N,1) AS chr
FROM Tally AS T
WHERE LEN(D.string) >= T.N
) AS chars
WHERE D.string LIKE '%' + REPLICATE(chr,3) + '%'
) AS CA
-- Gianluca Sartori
August 30, 2011 at 9:11 am
Cadavre (8/30/2011)
peanuts91 (8/30/2011)
Thanks Gianluca.But If I have a field containing for example 'Abraham', it will detect the A appears 3 times. But not 3 times in a row like aaa.
How's this?
DECLARE @table AS TABLE (name VARCHAR(100))INSERT INTO @table (name)
SELECT 'Saaaateres' AS name UNION ALL
SELECT 'NoRepeats' UNION ALL
SELECT 'aabbbcdef' UNION ALL
SELECT 'abraham'
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
t5 AS (SELECT 1 N FROM t4 x, t4 y),
tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
FROM t4 x, t4 y)
SELECT name, chr, repeats
FROM @table AS D
CROSS APPLY (SELECT chr, rptchr, MIN(row) AS row, MAX(rnk) AS repeats
FROM (SELECT SUBSTRING(D.name,number,1) AS chr,
RANK() OVER (PARTITION BY SUBSTRING(D.name,number,1) ORDER BY number) AS rnk,
REPLICATE(SUBSTRING(D.name,number,1), 3) as rptchr,
SUBSTRING(D.name,number,3) AS row
FROM tally AS T
WHERE LEN(D.name) >= T.number) AS ranks
GROUP BY chr, rptchr) AS CA
WHERE rptchr = row
Nice code, Cadavre!
-- Gianluca Sartori
August 30, 2011 at 9:43 am
Perfect Guys!!
thank you so much!
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply