logics for some repetitive characters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

logics for some repetitive characters

Author

Topic

shilpash
Posting Yak Master

103 Posts

Posted - 2014-07-09 : 13:32:43

I need some logics for these--
1) check for repetitive characters--like 5555,4444,1111

2)check for sequential series--like 4567,5678,2345 or 5432,8765,7654 Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-09 : 15:55:48

[code]DECLARE @Sample TABLE
(
Data VARCHAR(10) NOT NULL
);

INSERT @Sample
(
Data
)
VALUES ('5555'),
('4444'),
('1111'),
('4567'),
('5678'),
('2345'),
('5432'),
('8765'),
('7654'),
('1342'),
('1235');

-- Repetitive characters
SELECT Data
FROM @Sample
WHERE Data NOT LIKE '%[^' + SUBSTRING(Data, 1, 1) + ']%';

-- Sequential series
WITH cteSource(Data, Ascending, Descending)
AS (
SELECT s.Data,
SUBSTRING(s.Data, v.Number, 1) - v.Number AS Ascending,
SUBSTRING(s.Data, v.Number, 1) + v.Number AS Descending
FROM @Sample AS s
INNER JOIN master.dbo.spt_values AS v ON v.[Type] = 'P'
AND v.Number BETWEEN 1 AND LEN(s.Data)
)
SELECT Data
FROM cteSource
GROUP BY Data
HAVING MIN(Ascending) = MAX(Ascending)
OR MIN(Descending) = MAX(Descending);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

shilpash
Posting Yak Master

103 Posts

Posted - 2014-07-12 : 13:25:48

Awsome.This works.Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources