how to use soundex in sql..?

Question

3.00/5 (2 votes)

See more:

select * from sub where SOUNDEX(C_Name)=SOUNDEX(select territory from up where territory not like 'sa0%')

I want to check the column c_name from sub table and column territory from up table
using soundex find some matching values in both table but iam getting error pls help me out

Posted 28-Apr-15 2:23am

Member 11337367

Add a Solution

3 solutions

Solution 2

Your subquery

SOUNDEX(select territory from up where territory not like 'sa0%')

can, and probably will, return multiple rows, or at least it would if it was valid SQL, which it isn't. The following may work

SQL

select sub.* from sub
inner join up on SOUNDEX(up.territory)  = SOUNDEX(c_name)
where up.territory not like 'sa0%'

Posted 28-Apr-15 2:45am

Chris Quinn

Comments

Sascha Lefèvre 28-Apr-15 9:09am

+5

Solution 3

The error you're getting isn't directly related to SOUNDEX. It could be any other function that requires a scalar value and it would be the same error, because your subquery returns a resultset, not a scalar value.

Solution 2 is probably what you want.

Syntactically the following query would also work, but (most certainly) not get the result you want (because it compares only against one SOUNDEX(territory) where territory doesn't start with 'sa0'). But it might give you some more insight how it works what I explained in the first paragraph and help you with other queries:

SQL

select * from sub where SOUNDEX(C_Name) = SOUNDEX((select top 1 territory from up where territory not like 'sa0%'));

The double parenthesis is actually syntactically neccessary here.

Posted 28-Apr-15 3:09am

Sascha Lefèvre

Updated 28-Apr-15 3:59am

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

deepankarbhatnagar · Accepted Answer · 2015-04-28T02:35:00

Solution 1

Hi, hope these links below will help.

Thanks.

SOUNDEX() in Microsoft SQL 2005[^]

http://www.4guysfromrolla.com/webtech/tips/t103101-1.shtml[^]

Posted 28-Apr-15 2:35am

deepankarbhatnagar

Comments

Sascha Lefèvre 28-Apr-15 8:39am

That completely ignores his problem.

deepankarbhatnagar 28-Apr-15 8:41am

I think you have confuse in what you requires.

Member 11337367 28-Apr-15 8:44am

could you correct my queries..am getting error for that like below
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.

Sascha Lefèvre 28-Apr-15 8:55am

Take a look at solution 2 - that's what I meant. It's not directly related to SOUNDEX, it could be any function that requires a scalar value. But his subquery doesn't return a scalar but a resultset and that's why he's getting an error.

deepankarbhatnagar 28-Apr-15 8:52am

SOUNDEX function never contains a sub query, it only contains a field