SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 logics for some repetitive characters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shilpash
Posting Yak Master

103 Posts

Posted - 07/09/2014 :  13:32:43  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 07/09/2014 :  15:55:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

shilpash
Posting Yak Master

103 Posts

Posted - 07/12/2014 :  13:25:48  Show Profile  Reply with Quote
Awsome.This works.Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000