CODEWITHSUNDEEP
sqlsubstringcharactertableau-apicharindex
user3381842
user3381842

Reputation: 21

Extract characters from string using SQL

I have a column of ID's and I am needing to use SQL to extract the first two characters after the " - "

I am using the Custom SQL Query in Tableau 10.4

Example Column:

ABC - BCA - IT
AD - HD - A
QWE - QWE - E

What I need:

BC
HD
QW

I have tried to use the substring and Charindex function, but cannot seem to get it. Any ideas?

Upvotes: 2

Views: 11769

Answers (4)

ccarpenter32
ccarpenter32

Reputation: 1077

It's been a while since I've worked with Tableau, but IIRC this would be something this should work:

LEFT(TRIM(SPLIT([Column], `-`, 2)), 2)

To the best of my knowledge, no version of Tableau supports substring.

Alternatively, this might also help:

MID([Column], FIND([Column], "-") + 2, 2)

Upvotes: 1

spencer7593
spencer7593

Reputation: 108510

With MySQL, there are a lot of possible expressions. The big difference is handling of string values that don't contain exactly two dash characters.

MySQL provides a handy SUBSTRING_INDEX function.

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index

Assuming the string values contain exactly two dash characters, then something like this:

 SELECT c.foo
      , TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(c.foo,'-',2),'-',-1)) AS mid
   FROM ( 
          SELECT  'ABC - BCA - IT' AS foo
          UNION ALL SELECT 'AD - HD - A'
          UNION ALL SELECT 'QWE - QWE - E'
        ) c

Omit the TRIM function if we don't want to remove the leading and trailing spaces.

foo              mid
---------------  ---
ABC - BCA - IT   BCA
AD - HD - A      HD
QWE - QWE - E    QWE

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

If you are working with SQL Server, then you can use substring() function :

select substring(col, charindex('-', col) + 1, 2)) as need

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271031

You can use:

select substring(example, charindex(' - ', example) + 3, 2)
from (values ('ABC - BCA - IT'), ('AD - HD - A')) v(example)

Upvotes: 1

Related Questions