May 7, 2015 at 10:11 am
I am interested in creating a query that will test if a value is the same in a particular field.
For example, if the value is "0", or "000", or "000000" or "333", "444444", I would like to extract it. Otherwise omit the value.
May 7, 2015 at 10:20 am
i did not write this, but i saved a copy from 2011. search for some strings in the code to find the ooriginal thread here:
Find String like 'aaaaa' or 'bbbbbb' or 'cccccc' etc....
this assumes 3 or more repeating chars in a row is the data validation you want to look for:
;with data (string) as (
SELECT 'Saaaateres'
UNION ALL
SELECT 'NoRepeats'
UNION ALL
SELECT 'aabbbcdef'
),
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, MAX(rnk) AS repeats
FROM (
SELECT SUBSTRING(D.string,N,1) AS chr, RANK() OVER (PARTITION BY SUBSTRING(D.string,N,1) ORDER BY N) AS rnk
FROM tally AS T
WHERE LEN(D.string) >= T.N
) AS ranks
GROUP BY chr
HAVING MAX(rnk) >= 3
) AS CA
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
--------------------------------------------------------------------------------
Lowell
May 7, 2015 at 10:25 am
thank you Lowell!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply