How do I pull out a portion of a string?

Report · Nov 01, 2013

I have over 2,000 emails in mySQL database with company names as well. Unfortunately, these subscribers do not enter the company name the same. So I came up with the idea to pull the company name from the right side of the @ symbol, ie: yourname@company.com. But try as I may, I have not been able to figure out how to do this. Can anyone help me out here?

Report · Nov 01, 2013

Are you trying to do it in CFML code or directly in mySQL?

If CFML, you can treat the email addresses as lists, and parse with ListLast() using the @ as the delimiter. You could then parse off the TLD part of the address using ListFirst() and the "." as the delimiter.

I'm not all that familiar with mySQL (I use MSSQL), but you could use LOCATE() to find the @ character, and use that position in a SUBSTRING() to parse off the domain name. Then do another LOCATE()/SUBSTRING() on the "." character.

HTH,

-Carl V.

Report · Nov 01, 2013

Hey Carl, I am trying to send emails to all subscribers of one particular company. For instance say I want to find all employees in my list from dell. So, I would query all emails looking for those with dell.com after the @ symbol and send them an email with Coldfusion. I tried your techniques above to great satisfaction. Here is what I did:

SELECT email

FROM users

</CFQUERY>

</head>

<body>

<ol>

<li>#myList#</li>

</cfoutput>

</ol>

How can I add this into the above query to only go out to the company of my choice, say all dell employees?

Report · Nov 01, 2013

If you assume the company name will always exactly match the domain name, you could do this in a WHERE clause of your query. Assuming you submit a form to start the email process, and the form has a field called "CompanyName":

<CFQUERY Name="Get" datasource="#application.dsn#">
      SELECT email
      FROM users
     WHERE email LIKE <cfqueryparam value="%@#form.CompanyName#.%" cfsqltype="cf_sql_varchar">
</CFQUERY>

NOTE: If you are submitting queries and passing values into the query based on user input (like FORM or URL variables), ALWAYS use CFQUERYPARAM to prevent SQL Injection attacks.

Anyway, this should select all the user email addresses that contain the company name between the "@" and "." characters. It will also not give false positives when searching for "Dell" and there are email addresses with domains like "silverdell.com" or "delltaco.com".

-Carl V.

Report · Nov 01, 2013

That is too sweet and simple Carl. Thanks.