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

Community Beginner ,
Dec 30, 2020 Dec 30, 2020

Copy link to clipboard

Copied

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.

 

 

TOPICS
Advanced techniques

Views

177

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Advocate , Dec 31, 2020 Dec 31, 2020
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.

Likes

Translate

Translate
Advocate ,
Dec 30, 2020 Dec 30, 2020

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Dec 30, 2020 Dec 30, 2020

Copy link to clipboard

Copied

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!

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Dec 30, 2020 Dec 30, 2020

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Dec 30, 2020 Dec 30, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 31, 2020 Dec 31, 2020

Copy link to clipboard

Copied

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 .

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Dec 31, 2020 Dec 31, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Dec 31, 2020 Dec 31, 2020

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Dec 31, 2020 Dec 31, 2020

Copy link to clipboard

Copied

LATEST

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines