Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Like column name in another column name

 
M Wilson
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SQL DB2
How do you do a like on column name of one column to another (not an actual value)
For example
last name is Smith
Full name is John Smith

LOCATE(A.FULL_NAME, B.LAST_NM) <> 0

This won't return anything, it doesn't seem to find just the last name part 'Smith' in the full name value 'John Wilson'

Searching around I've tried B.LAST_NM LIKE '%'||A.FULL_NAME||'%' which I don't think it's right syntax that is throwing syntax error.

How do you like a column name for another column name?

Thanks much!
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
LIKE is just an operator, so it really shouldn't care whether the operands are columns or string literals.

It would be better if you could post the complete queries you've tried, as well as the database you're using - the second excerpt you've posted looks like it is written in Oracle's or similar dialect, while the first one (the LOCATE function) looks like it belongs to another database.
 
M Wilson
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It doesn't like: B.LAST_NM LIKE A.FULL_NAME

SELECT *
FROM TABLEA AS A, TABLEB AS B
WHERE
B.LAST_NM LIKE A.FULL_NAME
AND A.ID = B.ID

again gives syntax error

Thanks much!!!
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
M Wilson wrote:It doesn't like B.LAST_NM LIKE A.FULL_NAME, B.LAST_NM

Well, that is not a valid syntax (at least not in databases I know - again, which database do you use?).

B.LAST_NM LIKE A.FULL_NAME should be enough, why are you putting , B.LAST_NM in?
 
K. Tsang
Bartender
Posts: 3585
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are looking for the index at which search string is in the source string then either LOCATE or POSITION function should do the job.

The problem you had was the search string and source string are reversed!

LOCATE(search_string, source_string)
POSITION(search_string, source_string)

There is a string version note the parms are reversed
LOCATE_IN_STRING(source_string, search_string)
POSSTR(source_string, search_string)

 
M Wilson
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

B.LAST_NM LIKE A.FULL_NAME should be enough, why are you putting , B.LAST_NM in?

DB2 SQL
The full name may be a business or trust name. The person's last name may or may not be part of that full name.

For example business name is WILSON'S GARDEN which is not a match to John Smith but is a match to Jane Wilson.

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic