Skip to main content
S
seasonedweb
Inspiring
December 30, 2020
Answered

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

  • December 30, 2020
  • 8 replies
  • 871 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>

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

 

 

This topic is closed to new replies. Start a new post to keep the conversation going.
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.

8 replies

EddieLotter
EddieLotterCorrect answer
Inspiring
December 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.

    S
    seasonedwebAuthor
    Inspiring
    December 31, 2020

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

    EddieLotter
    EddieLotter
    Inspiring
    December 30, 2020

    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
    December 30, 2020

    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
    EddieLotter
    Inspiring
    December 30, 2020

    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>
      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices