Inspiring

Answered

Select the first word of a text string, use in query LIKE operator?

Forum|Forum|5 years ago
December 30, 2020
2 replies
850 views

Greetings

I have a text string containing the last names of a people (5000+ in DB), but in this case, alias names follow delimited by spaces. French last names have "dit" (Smith dit Smythe dit Smitt) names.

I would like to retrieve only the first (last) name to use in a dynamic query.

so,

<cfquery name="trim_last_name" datasource="#Request.BaseDSN#">
SELECT ged_lastname
FROM ged_main
</cfquery>

<cfquery name="match_carignan_name" datasource="#Request.BaseDSN#">
SELECT *
FROM carignan_main, ged_main
WHERE carignan_lname LIKE #clean_last_name#
AND carignan_fname LIKE ged_firstname
ORDER BY clean_last_name ASC</cfquery>

</cfoutput>

This is wrong, of course, and the result shows only one last name, which actually does not have additional "dit" last names.

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name

There is more than one way to do this- using SQL alone, or split the burden?

Thanks in advance for your time

Norman B.

Advanced techniques

This topic has been closed for replies.

Correct answer EddieLotter

You can also try the following to get a full result set from a single query:

<cfquery name="match_ged_lastname_partial" datasource="#Request.BaseDSN#">
SELECT *
FROM carignan_main, ged_main
WHERE ged_lastname LIKE carignan_lname + '%'
AND carignan_fname = ged_firstname
ORDER BY carignan_lname ASC
</cfquery>

However, you could get unwanted matches. You really should be joining a primary key to a foreign key. But this is a database issue, not a ColdFusion issue.

EddieLotterCorrect answer

Inspiring

You can also try the following to get a full result set from a single query:

<cfquery name="match_ged_lastname_partial" datasource="#Request.BaseDSN#">
SELECT *
FROM carignan_main, ged_main
WHERE ged_lastname LIKE carignan_lname + '%'
AND carignan_fname = ged_firstname
ORDER BY carignan_lname ASC
</cfquery>

However, you could get unwanted matches. You really should be joining a primary key to a foreign key. But this is a database issue, not a ColdFusion issue.

S

seasonedwebAuthor

Inspiring

Thank you so much- that is the exactly what I needed.

EddieLotter

Inspiring

Use cfqueryparam for ~~both~~ clean_last_name and ged_firstname in the match_carignan_name query.

I just noticed that you're using ged_firstname as a join condition. You will also need to append a wildcard operator to the clean_last_name variable for the LIKE operator to do what you want.

S

seasonedwebAuthor

Inspiring

Thank you for your answer- I would need to see the actual script in order for me to get what I'm looking for.

so,

<cfquery name="match_carignan_name" datasource="#Request.BaseDSN#">
SELECT *
FROM carignan_main, ged_main
WHERE carignan_lname LIKE ged_lastname
AND carignan_fname LIKE ged_firstname
ORDER BY ged_lastname ASC</cfquery>

I think there must be a simple solution using this query?

It's the "ged_lastname" that, unfortunately, can have several other last names attached.

Could it be something like:

WHERE carignan_lname LIKE (only select the first name in the string)?

Sorry if I seem obtuse, but I would need to see the whole thing with the proper format to solve- Thanks again!

EddieLotter

Inspiring

Try the following, but note that I didn't have time to test:

<cfset clean_last_name = listfirst(trim_last_name.ged_lastname, " ") & "%">
<cfquery name="match_carignan_name" datasource="#Request.BaseDSN#">
SELECT *
FROM carignan_main, ged_main
WHERE carignan_lname LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#clean_last_name#">
AND carignan_fname = ged_firstname
ORDER BY clean_last_name ASC</cfquery>

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded