August 26, 2011 at 7:54 am
Hi guys,
In order to clean up some tables in my database I would like to find all records like 'aaaaaaaa' or 'bbbb' or 'ccccc'....'zzzzz'.
Is there any functions existing to do that?
thanks for your help.
best regards,
August 26, 2011 at 8:09 am
peanuts91 (8/26/2011)
Hi guys,In order to clean up some tables in my database I would like to find all records like 'aaaaaaaa' or 'bbbb' or 'ccccc'....'zzzzz'.
Is there any functions existing to do that?
thanks for your help.
best regards,
Sure, use LIKE!
select * from table
where fielda like 'aaaaaaaaaaaaaa%' -- starts with aaaaaaaaaaaaaa
or fieldb like '%zzzzz%' --contains zzzzz (slower performance with this one)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 26, 2011 at 8:17 am
Thanks Phil,
but actually I need to check if all characters in my string are the same without to know the length of my string.
Tell me if I'm not clear and sorry for my approximative english.
thanks
August 26, 2011 at 8:24 am
peanuts91 (8/26/2011)
Thanks Phil,but actually I need to check if all characters in my string are the same without to know the length of my string.
Tell me if I'm not clear and sorry for my approximative english.
thanks
Without knowing the length of the string, or the character which may be repeated?
Can you provide a few examples?
What about just looking for a minimum of three or four repeats? (So that anything repeated more than, say, three times is selected?)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 26, 2011 at 8:31 am
Now you're looking for a patternmatcher, which in this case would entail using CLR Regex.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 26, 2011 at 8:34 am
Here a table :
firstname lastname
-------------------- --------------------
aaaaaaa ccccccccccc
Jane Smith
John Doe
Paul zzzz
I would like to find in this table all records with the firstname or the lastname like 'same letters X times' where X is unknown, so like 'aaaaaaa%' doesn't work.
thanks
August 26, 2011 at 8:38 am
I think this is what you are looking for.
WITH Test(TestField) AS (
SELECT 'aaa'
UNION
SELECT 'abc'
UNION
SELECT 'bbb'
UNION
SELECT 'bcd'
UNION
SELECT 'ccc'
UNION
SELECT 'cccccc'
UNION
SELECT 'cccccccccccccccccccccccc'
)
SELECT *
FROM Test
WHERE NOT TestField LIKE '%[^' + Left(TestField,1) + ']%'
It basically looks for strings that don't contain a character that's not the first character in the string.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 26, 2011 at 8:42 am
drew.allen (8/26/2011)
I think this is what you are looking for.
WITH Test(TestField) AS (
SELECT 'aaa'
UNION
SELECT 'abc'
UNION
SELECT 'bbb'
UNION
SELECT 'bcd'
UNION
SELECT 'ccc'
UNION
SELECT 'cccccc'
UNION
SELECT 'cccccccccccccccccccccccc'
)
SELECT *
FROM Test
WHERE NOT TestField LIKE '%[^' + Left(TestField,1) + ']%'
It basically looks for strings that don't contain a character that's not the first character in the string.
Drew
Thanks very much Drew, it is what I need!!!
August 26, 2011 at 9:02 am
drew.allen (8/26/2011)
I think this is what you are looking for.
WITH Test(TestField) AS (
SELECT 'aaa'
UNION
SELECT 'abc'
UNION
SELECT 'bbb'
UNION
SELECT 'bcd'
UNION
SELECT 'ccc'
UNION
SELECT 'cccccc'
UNION
SELECT 'cccccccccccccccccccccccc'
)
SELECT *
FROM Test
WHERE NOT TestField LIKE '%[^' + Left(TestField,1) + ']%'
It basically looks for strings that don't contain a character that's not the first character in the string.
Drew
Wow, cool code!
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 26, 2011 at 9:37 am
Another method:
WITH Test(TestField) AS (
SELECT 'aaa'
UNION
SELECT 'abc'
UNION
SELECT 'bbb'
UNION
SELECT 'bcd'
UNION
SELECT 'ccc'
UNION
SELECT 'cccccc'
UNION
SELECT 'cccccccccccccccccccccccc'
)
SELECT *
FROM Test
WHERE TestField <> REPLICATE(LEFT(TestField,1), LEN(TestField))
-- Gianluca Sartori
August 26, 2011 at 10:38 am
Gianluca Sartori (8/26/2011)
Another method:
WITH Test(TestField) AS (
SELECT 'aaa'
UNION
SELECT 'abc'
UNION
SELECT 'bbb'
UNION
SELECT 'bcd'
UNION
SELECT 'ccc'
UNION
SELECT 'cccccc'
UNION
SELECT 'cccccccccccccccccccccccc'
)
SELECT *
FROM Test
WHERE TestField <> REPLICATE(LEFT(TestField,1), LEN(TestField))
WOW....Gian, good code 🙂
August 30, 2011 at 6:31 am
Thanks very much all for your very useful answers!!
I have a last request : How can I fin duplicates characters in a string . I mean I would like to detect when I have a minimum a 3 same characters in a row in a string.
For example : In the string Saaaateres I would like to detect the 3 'a' in a row.
In the string abcddddddddfgh I would like to detect the 8 'd' in a row etc...
thank you.
August 30, 2011 at 7:26 am
This should do:
;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
If you have a permanent Tally table, you can get rid of the CTEs tenRows and Tally.
Hope this helps
Gianluca
-- Gianluca Sartori
August 30, 2011 at 7:45 am
Gianluca Sartori (8/30/2011)
This should do:
;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
If you have a permanent Tally table, you can get rid of the CTEs tenRows and Tally.
Hope this helps
Gianluca
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.
August 30, 2011 at 8:25 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.
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply