Invalid area code - 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)

Invalid area code

Author

Topic

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-10-24 : 13:42:58

I've been tasked with updating a report to include any phone numbers where the area code digits are repeating, like 111 or 222 etc.

The condition prior to this is as follows:

WHERE LEN(isnull(temp.BPHomePhone, '')) < 10
OR DeptPA.dbo.find_regular_expression(temp.BPHomePhone, '^(\d)\1*$', 0) = 1

How can I add the new condition to the existing code?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 13:49:45

Add another regular expression for the area code:

'^(\d{3})\1*$', 0)

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-10-24 : 14:00:02

I was not the original author of the code and that function appears to be a system function. I tried to make sense of that line but I don't know how to interpret those characters... Can you give me a brief explanation of '^(\d{3})\1*$', 0)? Thanks

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 14:36:18

sorry man you'll have to look up the regular expression:

basically find repeat digit 3x is something like this:
^(\d.*)\1\1

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 14:39:41

I guess you can do the left 3 of digits:

WHERE LEN(isnull(temp.BPHomePhone, '')) < 10
OR DeptPA.dbo.find_regular_expression(temp.BPHomePhone, '^(\d)\1*$', 0) = 1
OR DeptPA.dbo.find_regular_expression(left(temp.BPHomePhone,3), '^(\d)\1*$', 0) = 1

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-10-24 : 15:03:27

Thank you... I was searching for any article relating to this and found this article:

http://www.sqlteam.com/article/regular-expressions-in-t-sql

I was just wondering how the syntax is written.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-24 : 15:47:45

WHERE SUBSTRING(Col1, 1, 1) = SUBSTRING(Col1, 2, 1)
AND SUBSTRING(Col1, 2, 1) = SUBSTRING(Col1, 3, 1)

E 12°55'05.63"
N 56°04'39.26"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-25 : 01:12:02

try something like this


DECLARE @T TABLE (Val VARCHAR(100))

INSERT INTO @T
SELECT 11 UNION ALL
SELECT 20 UNION ALL
SELECT 202 UNION ALL
SELECT 111 UNION ALL
SELECT 222

SELECT Val
FROM @T
WHERE LEN(Val) - LEN(REPLACE(Val, SUBSTRING(Val, 1, 1), '')) = LEN(Val)

"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources