sql help - 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)

sql help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-21 : 07:19:02

I have 1 table called reg that has a field phonenumber

I have a second table called dialcodes with a field code

dialcode has the start of all the numbers

I want to select * from dialcodes where the beginnign of the phone number is = to a dialcode from the dialcode table

so for example if phone number is

7735226522 and I do select * from dialcodes where '7735226552' like dialcode then it should return from dial code 773522

can someone help me with this?

(Dial code does not have the complete number but the beginning of the number (and not always the same amount of characters)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-21 : 07:57:39

Please post table structure, sample data and expected output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-21 : 09:00:12

table structure

is

reg (tablename)
name
phonenumber

dialcodes(tablename)
code
geographic

output would be where phonenumber='77385563222'
it shoudl find the code of 773855

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 09:04:25

select *
from reg as r
inner join dialcodes as c on r.phonenumber like c.code + '%'

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-21 : 14:32:01

thanks will this choose the closest match or the first record that will match some of those number?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 14:46:49

Exact match for all records.
Why don't you tell use what you are trying to accomplish?
Post some sample data and your expected output, and we will assist you.

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-21 : 14:57:57

I have phone numbers and I have a huge list of numbers and I need to match each number with the closest match in numbers.

I did
select * from dialcodes where '7735551222' like code + '%'

this should return code of 77355512 as that's the closest match

there may also be in the table other matches such as
773
and
77355
and
773555

but how can I get the closest match? (now i think it just returns a recordset with all possible matches)

MuffinMan
Posting Yak Master

107 Posts

Posted - 2007-03-21 : 15:02:20

select TOP 1 *
from dialcodes
where '7735551222' like code + '%'
ORDER BY len(code) desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources