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

[seasonedweb]

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>

<cfset clean_last_name = listfirst(trim_last_name.ged_lastname, " ")>

<cfoutput query="trim_last_name">

<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.

[EddieLotter]

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.

[seasonedweb]

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]

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>

[seasonedweb]

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

<cfset clean_last_name = listfirst(trim_last_name.ged_lastname, " ") & "%">

<cfoutput>

<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>

</cfoutput>

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'clean_last_name'.

The error occurred in C:/inetpub/wwwroot/CHICOPEECLAN/LAPOLICE-DESLAURIERS/WEBSITE/admin/cross_search_carignan.cfm: line 13

11 : SELECT * 12 : FROM carignan_main, ged_main 13 : WHERE carignan_lname LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#clean_last_name#"> 14 : AND carignan_fname = ged_firstname 15 : ORDER BY clean_last_name ASC</cfquery></cfoutput>

[BKBK]

You get the error because the expression #clean_last_name# attempts to evaluate a variable whose name includes the character %. A possible way out is:

<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#%">

 Also note that it is unnecessary to use the cfoutput tag .

[EddieLotter]

You're getting that error because clean_last_name is not a valid column name in the ORDER BY clause. Use carignan_lname instead.

[EddieLotter · Accepted solution]

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.

[seasonedweb]

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