how to check all the same number

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

General SQL Server Forums

New to SQL Server Programming

how to check all the same number

Author

Topic

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2011-01-24 : 17:18:01

I have a column of PhoneNumber . How to check the phoneNumber is all the same number (e.g. 2222222222)

what is the query for the above one??

dev

Zoumaho
Starting Member

5 Posts

Posted - 2011-01-24 : 20:30:44

Declare @str varchar(12)
SET @str = '222222222222'
IF REPLICATE(substring(@str,1,1), LEN(@str))=@str
print 'Yea'
ELSE
print 'Nooo'

We need to exercise not only the body or mind, but every aspect of our lives and more if it needs improvement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-25 : 03:08:16

Excellent suggestion, but replace LEN with DATALENGTH just in case @set ends with spaces.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-25 : 03:11:02

or

Declare @str varchar(12)
SET @str = '222222222222'
IF REPLACE(@str,LEFT(@str,1),'')=''
print 'Yea'
ELSE
print 'Nooo'

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-25 : 04:10:06

Madhi, change your @str to

SET @str = '222222222222' + CHAR(32)

and try again.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-25 : 05:09:10

It is ignored by both the methods

Declare @str varchar(15)
SET @str = '222222222222'+CHAR(32)
IF REPLACE(@str,LEFT(@str,1),'')=' '
print 'Yea'
ELSE
print 'Nooo'

go

Declare @str varchar(15)
SET @str = '222222222222'+CHAR(32)
IF REPLICATE(substring(@str,1,1), LEN(@str))=@str
print 'Yea'
ELSE
print 'Nooo'

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-25 : 06:05:51

Yes, but by adding a space to the string then it is not all "2", right?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-25 : 07:27:04

quote:
Originally posted by Peso

Yes, but by adding a space to the string then it is not all "2", right?

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

Ok. If trailing space is important it should be handled differently

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-25 : 07:31:50

Here is the alternate

Declare @str varchar(15)
SET @str = '222222222222'+CHAR(32)
if patindex('%[^'+LEFT(@str,1)+']%',@str)=0
print 'Yea'
ELSE
print 'Nooo'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources