Select problem - 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)

Select problem

Author

Topic

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-09 : 11:38:48

Hi,
I have a table with 2 columns (ID,Info). ID field have some unique id for each record. The problem is the Info field. In some records it contains telephone number in some records email address or web site address. I need to select the records which have telephone number in their Info field.

I know some ways but I think they are not good. I am looking for professional way.
ID Info
154 (111) 768 3654
155 245-764-888
157 (306) 234-9146
197 dsfgfgsfg@hotmail.com
198 (666) 876-3833
231 aaaaaaaaaa
872 dkvjsdlfjiv
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 11:48:03

If your phone numbers are all in correct format with numbers and (,),- characters alone use this:-

SELECT ID,Info FROM YourTable WHERE CHARINDEX('@',Info) =0 AND CHARINDEX('.',Info) =0

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-09 : 11:51:35

Sometimes there is a phras in the Info column what can I do for them?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 12:08:58

quote:
Originally posted by Sep410

Sometimes there is a phras in the Info column what can I do for them?

Then isnt it enough to check that your fielkd dont have any alphabets. i.e

SELECT ID,Info FROM YourTable WHERE CHARINDEX('[a-z]',Info) =0 AND CHARINDEX('[A-Z]',Info) =0

davidmal
Starting Member

19 Posts

Posted - 2008-05-09 : 12:16:21

I suggest you would want to add a 'type' column to describe what type of info is in the field. I would suggest creating a lookup table:
dbo.LU_InfoType
InfoTypeID | Value
=================
1 |Home Phone
2 |Cell Phone
3 |email
4 |phrase
etc(...)
then have the main table look like
ID |InfoTypeID| Info
===============================
154 | 2 |(111) 768 3654
155 | 1 |245-764-888
157 | 1 |(306) 234-9146
197 | 3 |dsfgfgsfg@hotmail.com
198 | 2 |(666) 876-3833
Then if you wanted to query email addresses:
Select * from table where InfoTypeID = 4

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-09 : 12:18:47

Dear visakh16,
I tried what you said but it didn't have any effect on the select result and I still have alphabets in the result.

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-09 : 12:21:14

Unfortunately I cannot change the table. This is an old database and I just have to use it as the way it is.

davidmal
Starting Member

19 Posts

Posted - 2008-05-09 : 13:18:51

Sep410 Hope this helps:

-- find probable phone #s
SELECT ID,Info FROM tbl1
WHERE PATINDEX('%[a-z]%',Info) = 0
AND PATINDEX('%[A-Z]%',Info) = 0

-- find email addresses
SELECT ID,Info FROM tbl1
WHERE PATINDEX('%@%',Info) > 0
AND (PATINDEX('%.__',Info) > 0
OR PATINDEX('%.___',Info) > 0
OR PATINDEX('%.____',Info) > 0)

-- find probable phrases
SELECT ID,Info FROM tbl1
WHERE NOT (
PATINDEX('%@%',Info) > 0
AND (PATINDEX('%.__',Info) > 0
OR PATINDEX('%.___',Info) > 0
OR PATINDEX('%.____',Info) > 0)
)
AND PATINDEX('%[A-Z]%',Info) > 0
AND PATINDEX('%[a-z]%',Info) > 0
Dave

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-09 : 13:26:01

Thank you Dave,It is working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources