number pattern - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

number pattern

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-03 : 02:03:47

hi everyone,

ive no idea where to post this question.. i guess somebody could help me with this :-
i need to validate if some long number match the pattern of these sequal number. The sequence as below, if u could see the pattern, it add 1 to the 2nd last number and reset last digit to 0 when it hit 6. See the sequence, and i need to come out with the pattern formula, so that i could validate any sequence given whether it match the pattern or not... Sequal :-

5277 2042
5277 2053
5277 2064
5277 2075
5277 2086
5277 2090

when we have that pattern and generate the formula, so i could test whether these given number is in the sequence or not...

eg: does this number belong to the pattern ?

1. 5277 2101 - YES
2. 5277 2112 - YES
3. 5277 2113 - No. because 2nd last digit is not incremented.
4. 5277 2031 - YES. Because it's valid before 2043 (last & 2nd digit incremented)

Hopefully someone could come out with an idea to generate this sequence formula.. thanks

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 02:10:29

Very easy. Make us of modula function and only extract second last digit with ( / 100 ) % 10. This value should be checked against % 7.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-03 : 02:10:51

quote:
ive no idea where to post this question

This is not the appropriate forum to post this. You should post it over at T-SQL forumn. Wait for Mod to move it out.

quote:
Hopefully ~~someone~~ Peter could come out with an idea to generate this sequence formula.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-03 : 02:11:46

Peter is here before i finished my post

KH
[spoiler]Time is always against us[/spoiler]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 02:20:45

My attempt:

Create function dbo.TestPattern
(
	@LastNum varchar(10),
	@NextNum varchar(10)
)
Returns bit
as
begin
	declare @Result bit

	select @Result = 
		case 
			when substring(@LastNum, datalength(@LastNum), 1) = '6' and substring(@NextNum, datalength(@NextNum), 1) = '0' then
				case 
					when cast(substring(@LastNum, datalength(@LastNum)-1, 1) as int) + 1 = cast(substring(@NextNum, datalength(@NextNum)-1, 1) as int) then 1
					when (cast(substring(@LastNum, datalength(@LastNum)-1, 1) as int) + 1) % 10 = cast(substring(@NextNum, datalength(@NextNum)-1, 1) as int) then 1
					else 0
				end
			when cast(substring(@LastNum, datalength(@LastNum), 1) as int) + 1 = cast(substring(@NextNum, datalength(@NextNum), 1) as int) then
				case 
					when cast(substring(@LastNum, datalength(@LastNum)-1, 1) as int) + 1 = cast(substring(@NextNum, datalength(@NextNum)-1, 1) as int) then 1
					when (cast(substring(@LastNum, datalength(@LastNum)-1, 1) as int) + 1) % 10 = cast(substring(@NextNum, datalength(@NextNum)-1, 1) as int) then 1
					else 0
				end
			else 0
		end

	return @Result
end

-- Test results
select dbo.TestPattern(52772090, 52772101)
select dbo.TestPattern(52772101, 52772112)
select dbo.TestPattern(52772112, 52772113)
select dbo.TestPattern(52772031, 52772042)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 03:11:38

Here is a more generic solution for any predefined pattern which still has the business rule to add 1 to both last digits and replace last digit to 0 if is 7.
It is also implied that the first four characters is some kind of grouping id (not part of sequence pattern) and total digits is 8.

CREATE FUNCTION	dbo.fnTestpattern
(
	@Sequence INT,
	@TestNum INT
)
RETURNS BIT
AS
BEGIN
	IF @Sequence / 10000 <> @TestNum / 10000
		RETURN 0

	DECLARE		@Valid BIT,
			@Pattern INT

	SET		@Pattern = @TestNum % 10000 - (@Sequence % 10000) % 70 + 70

	SELECT		@Valid = 1
	FROM		master..spt_values AS s1
	CROSS JOIN	master..spt_values AS s2
	WHERE		s1.Number BETWEEN 0 AND 143
			AND s1.Name IS NULL
			AND s2.Number BETWEEN 0 AND 6
			AND s2.Name IS NULL
			AND 70 * s1.Number + 11 * s2.Number = @Pattern

	RETURN		ISNULL(@Valid, 0)
END

Enjoy.

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-03 : 03:44:19

huh?? guys.. u all r very fast.. and again im struggling to understand ur code..

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-03 : 03:46:19

guys.. ive never made any function b4.. can i just paste the testing in the analyzer qithout creating any function?
and if i create the function.. how would i call it in analyzer?

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 03:48:30

I already shown in my post how to call function. See the section with comments - "Test results".

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 03:57:11

You should have highlighted the comment with green, Harsh

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-03 : 04:05:27

emmm.. actually this is not the sql thing.. so i posted at many other forum.. and lastly posted here.. but then i get the 1st solution here.. strange... haha.. what kind of alien living in this forum.. ? :D..

so far this workss.. will be back if i have any prob

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-03 : 04:07:07

with the asp.net ----> my topic is JUST Approved and publish minutes ago while u all working on the quick solution.. euiwwww

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 04:09:06

You should post a follow-up in other forums and tell them you've got a working solution here.
This way, you won't waste other peoples time.

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-03 : 04:17:32

ermm.. but then i also would like to see solution in asp.net vb.net..
posting the link here so they could meet the t-rex-Yak

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-03 : 04:18:40

ho How this thing work "mathematically". what is the formula

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 04:24:31

The formula reveals itself by investigating this number sequence as defined in original post.

0001	1003	2005	3000	4002	5004	6006	7001	8003	9005
0012	1014	2016	3011	4013	5015	6010	7012	8014	9016
0023	1025	2020	3022	4024	5026	6021	7023	8025	9020
0034	1036	2031	3033	4035	5030	6032	7034	8036	9031
0045	1040	2042	3044	4046	5041	6043	7045	8040	9042
0056	1051	2053	3055	4050	5052	6054	7056	8051	9053
0060	1062	2064	3066	4061	5063	6065	7060	8062	9064
0071	1073	2075	3070	4072	5074	6076	7071	8073	9075
0082	1084	2086	3081	4083	5085	6080	7082	8084	9086
0093	1095	2090	3092	4094	5096	6091	7093	8095	9090
0104	1106	2101	3103	4105	5100	6102	7104	8106	9101
0115	1110	2112	3114	4116	5111	6113	7115	8110	9112
0126	1121	2123	3125	4120	5122	6124	7126	8121	9123
0130	1132	2134	3136	4131	5133	6135	7130	8132	9134
0141	1143	2145	3140	4142	5144	6146	7141	8143	9145
0152	1154	2156	3151	4153	5155	6150	7152	8154	9156
0163	1165	2160	3162	4164	5166	6161	7163	8165	9160
0174	1176	2171	3173	4175	5170	6172	7174	8176	9171
0185	1180	2182	3184	4186	5181	6183	7185	8180	9182
0196	1191	2193	3195	4190	5192	6194	7196	8191	9193
0200	1202	2204	3206	4201	5203	6205	7200	8202	9204
0211	1213	2215	3210	4212	5214	6216	7211	8213	9215
0222	1224	2226	3221	4223	5225	6220	7222	8224	9226
0233	1235	2230	3232	4234	5236	6231	7233	8235	9230
0244	1246	2241	3243	4245	5240	6242	7244	8246	9241
0255	1250	2252	3254	4256	5251	6253	7255	8250	9252
0266	1261	2263	3265	4260	5262	6264	7266	8261	9263
0270	1272	2274	3276	4271	5273	6275	7270	8272	9274
0281	1283	2285	3280	4282	5284	6286	7281	8283	9285
0292	1294	2296	3291	4293	5295	6290	7292	8294	9296
0303	1305	2300	3302	4304	5306	6301	7303	8305	9300
0314	1316	2311	3313	4315	5310	6312	7314	8316	9311
0325	1320	2322	3324	4326	5321	6323	7325	8320	9322
0336	1331	2333	3335	4330	5332	6334	7336	8331	9333
0340	1342	2344	3346	4341	5343	6345	7340	8342	9344
0351	1353	2355	3350	4352	5354	6356	7351	8353	9355
0362	1364	2366	3361	4363	5365	6360	7362	8364	9366
0373	1375	2370	3372	4374	5376	6371	7373	8375	9370
0384	1386	2381	3383	4385	5380	6382	7384	8386	9381
0395	1390	2392	3394	4396	5391	6393	7395	8390	9392
0406	1401	2403	3405	4400	5402	6404	7406	8401	9403
0410	1412	2414	3416	4411	5413	6415	7410	8412	9414
0421	1423	2425	3420	4422	5424	6426	7421	8423	9425
0432	1434	2436	3431	4433	5435	6430	7432	8434	9436
0443	1445	2440	3442	4444	5446	6441	7443	8445	9440
0454	1456	2451	3453	4455	5450	6452	7454	8456	9451
0465	1460	2462	3464	4466	5461	6463	7465	8460	9462
0476	1471	2473	3475	4470	5472	6474	7476	8471	9473
0480	1482	2484	3486	4481	5483	6485	7480	8482	9484
0491	1493	2495	3490	4492	5494	6496	7491	8493	9495
0502	1504	2506	3501	4503	5505	6500	7502	8504	9506
0513	1515	2510	3512	4514	5516	6511	7513	8515	9510
0524	1526	2521	3523	4525	5520	6522	7524	8526	9521
0535	1530	2532	3534	4536	5531	6533	7535	8530	9532
0546	1541	2543	3545	4540	5542	6544	7546	8541	9543
0550	1552	2554	3556	4551	5553	6555	7550	8552	9554
0561	1563	2565	3560	4562	5564	6566	7561	8563	9565
0572	1574	2576	3571	4573	5575	6570	7572	8574	9576
0583	1585	2580	3582	4584	5586	6581	7583	8585	9580
0594	1596	2591	3593	4595	5590	6592	7594	8596	9591
0605	1600	2602	3604	4606	5601	6603	7605	8600	9602
0616	1611	2613	3615	4610	5612	6614	7616	8611	9613
0620	1622	2624	3626	4621	5623	6625	7620	8622	9624
0631	1633	2635	3630	4632	5634	6636	7631	8633	9635
0642	1644	2646	3641	4643	5645	6640	7642	8644	9646
0653	1655	2650	3652	4654	5656	6651	7653	8655	9650
0664	1666	2661	3663	4665	5660	6662	7664	8666	9661
0675	1670	2672	3674	4676	5671	6673	7675	8670	9672
0686	1681	2683	3685	4680	5682	6684	7686	8681	9683
0690	1692	2694	3696	4691	5693	6695	7690	8692	9694
0701	1703	2705	3700	4702	5704	6706	7701	8703	9705
0712	1714	2716	3711	4713	5715	6710	7712	8714	9716
0723	1725	2720	3722	4724	5726	6721	7723	8725	9720
0734	1736	2731	3733	4735	5730	6732	7734	8736	9731
0745	1740	2742	3744	4746	5741	6743	7745	8740	9742
0756	1751	2753	3755	4750	5752	6754	7756	8751	9753
0760	1762	2764	3766	4761	5763	6765	7760	8762	9764
0771	1773	2775	3770	4772	5774	6776	7771	8773	9775
0782	1784	2786	3781	4783	5785	6780	7782	8784	9786
0793	1795	2790	3792	4794	5796	6791	7793	8795	9790
0804	1806	2801	3803	4805	5800	6802	7804	8806	9801
0815	1810	2812	3814	4816	5811	6813	7815	8810	9812
0826	1821	2823	3825	4820	5822	6824	7826	8821	9823
0830	1832	2834	3836	4831	5833	6835	7830	8832	9834
0841	1843	2845	3840	4842	5844	6846	7841	8843	9845
0852	1854	2856	3851	4853	5855	6850	7852	8854	9856
0863	1865	2860	3862	4864	5866	6861	7863	8865	9860
0874	1876	2871	3873	4875	5870	6872	7874	8876	9871
0885	1880	2882	3884	4886	5881	6883	7885	8880	9882
0896	1891	2893	3895	4890	5892	6894	7896	8891	9893
0900	1902	2904	3906	4901	5903	6905	7900	8902	9904
0911	1913	2915	3910	4912	5914	6916	7911	8913	9915
0922	1924	2926	3921	4923	5925	6920	7922	8924	9926
0933	1935	2930	3932	4934	5936	6931	7933	8935	9930
0944	1946	2941	3943	4945	5940	6942	7944	8946	9941
0955	1950	2952	3954	4956	5951	6953	7955	8950	9952
0966	1961	2963	3965	4960	5962	6964	7966	8961	9963
0970	1972	2974	3976	4971	5973	6975	7970	8972	9974
0981	1983	2985	3980	4982	5984	6986	7981	8983	9985
0992	1994	2996	3991	4993	5995	6990	7992	8994	9996

Every value with same last digit is exactly X steps apart and repeats itself. In between, there are Y values who can be interpreted as offsets to the base value X. There!

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-03 : 04:31:04

from yur function.. what is this peter ?
master..spt_values

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 04:34:32

Try and find it. It is not hard.
If unsure, read in Books Online about system table and master database.
Also read about Fully Qualified Names.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 04:38:58

Or, if you don't bother nor have the will,

select * from master..spt_values where name is null

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-03 : 04:46:46

moved from script library.

i'm thinking about a better name for the script library forum

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-07-03 : 04:46:59

i dunno... what i expect to understand when i read topic
KEY_COLUMN_USAGE
Contains one row for each column, in the current blablablabla....

when im trying to search the spt_values keyword

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources