1 2 Previous Next 24 Replies Latest reply on Mar 17, 2015 11:01 AM by Steven Rouk

    Finding one or more letters in a string

    Brian Near

      Hi;

      I have a field of item numbers in string format.  Most contain all numbers (with some spaces), but some contain letters.  I'm trying to figure out how to build a filter to identify those records containing letters.  Here's an example of the data:

       

      1159404 002

      1456537 009

      3267119

      1178659

      16FB163 970  <-------

      1621320

       

      I know I can use CONTAINS for single search terms, but I'm stumped on how to search for a range of characters.

      Thanks for any guidance.

        • 1. Re: Finding one or more letters in a string
          Shawn Wallwork

          Brian, it ain't pretty but here's a calculated field you can use:

           

          ISNULL(INT(IIF(FIND([Numbers as String]," ")>0,

          LEFT([Numbers as String],FIND([Numbers as String]," ")-1) +

          RIGHT([Numbers as String],(LEN([Numbers as String])-FIND([Numbers as String]," "))),

          [Numbers as String])))

           

          Put it on the filter shelf and set to TRUE if you only want the alpha-numeric rows. One assumption I made was that there would never be more than 1 space in the string. It won't work if there's more than one.

           

          --Shawn

          2 of 2 people found this helpful
          • 2. Re: Finding one or more letters in a string
            Brian Near

            Hi Shawn;

             

            Unfortunately, I can't depend on there being only a single space. , or how many non-numeric characters there might be.

            I was hoping there was a way to specify ASCII values between 65 and 90 using AND somehow, but I guess it isn't in the cards.

             

            Will probably have to get our SQL Server guy to do a calculation to the data source.  Thanks very much for the reply!

            • 3. Re: Finding one or more letters in a string
              Shawn Wallwork

              Brian,

               

              A statement can be written that will accommodate more spaces; it's just longer and more tedious. And the way it's currently written any non-numeric number of characters will return a TRUE. But your SQL server guy solution is the better one.

               

              --Shawn

              2 of 2 people found this helpful
              • 4. Re: Finding one or more letters in a string
                Brian Near

                Thanks Shawn. I'm trying to understand your formula for future use.  It's slick, but I don't quite know how it works. :-/

                • 5. Re: Finding one or more letters in a string
                  Shawn Wallwork

                  Yeah nested stuff like this is tough to track.

                   

                  1. The ISNULL(INT(         rest of formula          )) basically tests to see if the results are an interger. If it's alpha-numeric the INT() will produce a NULL, which then makes the ISNULL() statement TRUE. If you just put the INT() around your field then all the rows with spaces will also return a NULL, which isn't what I thought you would want. So to separate the two, you have to test for spaces.

                   

                  2. The rest of the formula does this with a long IIF() statement, which takes the form of

                   

                       IIF( EXPRESSION, TRUE, FALSE).

                   

                  The EXPRESSION in this case is

                   

                       FIND([Numbers as String]," ")>0

                   

                  and is asking if there are any spaces " " in the field

                   

                  3. If this is TRUE, then we need to remove the space and put the string together so it returns a number instead of a NULL during the INT() test above.

                   

                       a. This: FIND([Numbers as String]," ") returns the position of the space. In your case it's always 8

                   

                       b. Once we know the position then we want to take all the spaces to the left of this and concatenate them to all the space to the right of the space; essentially eliminating the space.

                   

                       c. So  FIND([Numbers as String]," ")-1 is essentially 8-1=7 and then we use the LEFT() function to get the first 7 characters.

                   

                       d. Getting the last set of characters is a bit different. You take the overall length of the string LEN() and subtract the position of the space from it: Length-8 = # of characters remaining to the right of the space, which is the number of characters you want to count back from the end when using the RIGHT() function.

                   

                       f. The '+' concatenates these together, and it's ready for the integer test.

                   

                  4. The FALSE side of the IIF() statement simply returns all the non-alpha-numeric values that are left.

                   

                   

                  I must say these are a heck of a lot easier to write than explain! Hope this helps.

                   

                  --Shawn

                   

                  Message was edited by: DataViz Dude

                  1 of 1 people found this helpful
                  • 6. Re: Finding one or more letters in a string
                    Brian Near

                    Wow, thank you for taking the time to explain this calculation, Shawn.  I can see that it was more work than writing the original formula.

                    • 7. Re: Finding one or more letters in a string
                      Tony Van Deyl

                      I used FIND([City]," ") = 1 to identify all instances of there being a space for the first character, but how would this work for finding it for the last character if the field can potentially be any length?

                       

                      I tried combining some of your above formulas but i think im missing something.

                       

                      I'd like to try and set it for either first being a space or the last being a space.

                       

                      Thank you!

                       

                      Tony

                      • 8. Re: Finding one or more letters in a string
                        Toby Erkson

                        Tony,

                        You can use the STARTSWITH and ENDSWITH commands in the Calculated Field editor.  If you just want to get rid of leading & trailing spaces then use the TRIM command.  To get rid of all spaces you can use the REPLACE command like so:  REPLACE([yourField], " ", "").

                        • 9. Re: Finding one or more letters in a string
                          Joe Oppelt

                          Brian -- What is the real goal here?  Just to find out if there are non-numeric characters?

                           

                          If so, use the REPLACE function Toby mentioned.  Take out all the spaces.

                           

                          Then just do INT on the result.  If it comes up "null", you have non-numeric characters.

                           

                          If you want to find specific non-numeric characters, use the CHAR function.  It will return the ascii code for a given character.  You can get creative with that.

                           

                          If you want to find spcific string values (such as "XYZ") use the FIND function.  (...  FIND([char-variable], "XYZ") will return the position within the string where it is found, or 0 if not found.)  Or just use CONTAINS to say if it is in there somewhere.  (Result is "true" or "false".)

                          • 10. Re: Finding one or more letters in a string
                            Tony Van Deyl

                            Actually the purpose is to identify the records where the field was entered with a space in front or back because they are all in a database in a different dispatch software that prevents them from coming up in a search by city because of the space.

                             

                            So I need to identify then so i can manually correct them unless the programmers have a way of taking all the ID#s I give them and doing a mass delete first character on all the ones that are confirmed to have a space in that position.

                             

                            I actually tried something indirect and it seemed to work:

                             

                            For the end...

                             

                            if Right([City] , 1) = " " then "True" else "False" end

                             

                            And for the beginning

                             

                            if Left([City] , 1) = " " then "True" else "False" end

                             

                            Then i just created a calculated field combining the two, and filtered out falsefalse.

                             

                            Im sure there is a simpler way to do it but im still learning the programming language of Tableau. (with excel/VB background)

                            • 11. Re: Finding one or more letters in a string
                              Joe Oppelt

                              Tony -- The questions I asked were for Brian.  The thread is his.

                               

                              You can remove your leading- and trailing-spaces with the LTRIM and RTRIM functions.

                              • 12. Re: Finding one or more letters in a string
                                Toby Erkson

                                Tony Van Deyl wrote:

                                 

                                Actually the purpose is to identify the records where the field was entered with a space in front or back because they are all in a database in a different dispatch software that prevents them from coming up in a search by city because of the space.

                                 

                                So I need to identify then so i can manually correct them unless the programmers have a way of taking all the ID#s I give them and doing a mass delete first character on all the ones that are confirmed to have a space in that position.

                                 

                                I actually tried something indirect and it seemed to work:

                                 

                                For the end...

                                 

                                if Right([City] , 1) = " " then "True" else "False" end

                                 

                                And for the beginning

                                 

                                if Left([City] , 1) = " " then "True" else "False" end

                                 

                                Then i just created a calculated field combining the two, and filtered out falsefalse.

                                 

                                Im sure there is a simpler way to do it but im still learning the programming language of Tableau. (with excel/VB background)

                                In coding there's many ways to accomplish the same end result.  Another way to code your desired end result with a one-liner is

                                IIF(STARTSWITH( [City], " ") OR ENDSWITH( [City], " "), TRUE, FALSE)
                                
                                
                                

                                 

                                What's interesting (or confusing depending on the amount of caffeine in your brain) is that you cannot use a Boolean operator in an IF...THEN...END statement but you can use it in the IIF command 

                                • 13. Re: Finding one or more letters in a string
                                  Shawn Wallwork

                                  Hmmm, my caffeine-addled brain says if you want to produce a boolean result (TRUE/FALSE) then you don't need no IF of any kind:

                                   

                                  STARTSWITH( [City], ' ' ) OR ENDSWITH([City], ' ' )

                                   

                                   

                                   

                                  --Shawn

                                  • 14. Re: Finding one or more letters in a string
                                    Toby Erkson

                                    Yup, that will work as there are many ways.  I wouldn't use that because it's not readily self-documenting.  The IIF leaves no question to the end result.  Unless one is familiar with Tableau one could look at that and think, "if it starts or ends with a space then...what happens?"  Is it a TRUE/FALSE?  0/1?  YES/NO?  NULL?  It's unclear what would be returned.

                                     

                                    Now, if you added a comment then all would be forgiven:

                                    //Returns TRUE if either command returns TRUE, otherwise returns FALSE.
                                    STARTSWITH( [City], ' ' ) OR ENDSWITH([City], ' ' )
                                    
                                    

                                     

                                     

                                    The fact remains that IF...THEN...END is fickle towards Boolean.  I wonder if CASE statements are the same?

                                     

                                     

                                    I'm all about documentation because I have a bad memory.  Plus it helps others a LOT when they follow in my place.

                                    1 2 Previous Next