4 Replies Latest reply on Nov 1, 2013 1:47 PM by rickaclark54

    How do I pull out a portion of a string?

    rickaclark54 Level 1

      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?

        • 1. Re: How do I pull out a portion of a string?
          Carl Von Stetten Adobe Community Professional & MVP

          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.

          • 2. Re: How do I pull out a portion of a string?
            rickaclark54 Level 1

            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:

             

            <CFQUERY Name="Get" datasource="#application.dsn#">

            SELECT email

            FROM users

            </CFQUERY>

            </head>

             

            <body>

             

             

            <ol>

            <cfoutput query="Get">

            <cfset myExt = listLast(email,"@")>

            <cfset myList = listFirst(myExt, ".")>

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

            • 3. Re: How do I pull out a portion of a string?
              Carl Von Stetten Adobe Community Professional & MVP

              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.

              • 4. Re: How do I pull out a portion of a string?
                rickaclark54 Level 1

                That is too sweet and simple Carl. Thanks.