Unsupported SQL Functions in Workbench

Is anyone aware of some SQL functions being unsupported in Workbench? I have been using a small query that contains both charindex and substring.

When I run it directly against our database in SSMS it works fine, but it seems Workbench is not interpreting correctly.


It would be helpful to know if there's a document existing somewhere with supported/unsupported SQL functions.

Answers

  • GrantSmith
    GrantSmith Indiana 🔴

    What's the query you're running and the error message you're getting? (anonymize it if necessary)

  • SELECT DISTINCT MarketingClubLevel,

    CASE WHEN CHARINDEX(' ',MarketingClubLevel,1) = 0 THEN MarketingClubLevel 

                  ELSE SUBSTRING(MarketingClubLevel,1,(CHARINDEX(' ',MarketingClubLevel,1)))

                  END AS ClubDescription

                  FROM vClub


    Result in SSMS:

    Result in Workbench:


    Not necessarily getting an error, but as you can see as an example that "Diamond Premier" is not getting truncated to "Diamond" like in the result from SSMS above it.

  • GrantSmith
    GrantSmith Indiana 🔴

    what happens if you change the charindex check to be >=

    SELECT DISTINCT MarketingClubLevel,
    
    CASE WHEN CHARINDEX(' ',MarketingClubLevel,1) >= 0 THEN MarketingClubLevel
    
      ELSE SUBSTRING(MarketingClubLevel,1,(CHARINDEX(' ',MarketingClubLevel,1)))
    
      END AS ClubDescription
    
      FROM vClub
    

    What does `CHARINDEX(' ', MarketingClubLevel,1)` return between Workbench and SSMS?

    Also, are you using the correct driver verison in your connection?

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    translating 'what xyz platform is reading out of my odbc driver' can be a pain

    to start troubleshooting, break your query into pieces.

    SELECT DISTINCT MarketingClubLevel,
    
    -- is my query identifying the space?
    CHARINDEX(' ',MarketingClubLevel,1) as positionOfSpace,
    
    CASE WHEN CHARINDEX(' ',MarketingClubLevel,1) >= 0 THEN MarketingClubLevel
    
      ELSE SUBSTRING(MarketingClubLevel,1,(CHARINDEX(' ',MarketingClubLevel,1)))
    
      END AS ClubDescription
    
      FROM vClub
    


  • It appears that Workbench is reading charindex and substring individually, but when you combine the two together it is having issues.

    Here's the query I ran - took @jaeW_at_Onyx's version and added one for substring.

    SELECT DISTINCT MarketingClubLevel,
    -- is my query identifying the space?
    CHARINDEX(' ',MarketingClubLevel,1) as positionOfSpace,
    
    SUBSTRING(MarketingClubLevel,1,8) as SubstringTest, --Set an abritray end point for substring
    
    CASE WHEN CHARINDEX(' ',MarketingClubLevel,1) >= 0 THEN MarketingClubLevel
    
    
      ELSE SUBSTRING(MarketingClubLevel,1,(CHARINDEX(' ',MarketingClubLevel,1)))
    
    
      END AS ClubDescription
    
    
      FROM vClub
    
    
    

    Result:


  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    Luke ... i think your problem is your WHEN statement you've got it's ALWAYS grreater than or equal to zero so it will NEVER get to your ELSE clause.

    CASE
    WHEN CHARINDEX(' ',MarketingClubLevel,1) >= 0 THEN MarketingClubLevel
    ELSE SUBSTRING(MarketingClubLevel,1,(CHARINDEX(' ',MarketingClubLevel,1)))
    END AS ClubDescription
    

    rewrite it

    CASE
    WHEN CHARINDEX(' ',MarketingClubLevel,1) > 0 THEN SUBSTRING(MarketingClubLevel,1,(CHARINDEX(' ',MarketingClubLevel,1)))
    ELSE MarketingClubLevel
    END AS ClubDescription
    


  • Here's what I get when I run the that additional code. It's the "Club Description New" column. Tried tweaking a few things in the case to be <> 0 as well and still returning either the "club description" or "club description new" values.



  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    Instead of Substring try LEFT(description, <positionOfSpace>)

  • Just as an FYI, I don't believe Workbench is ever limited in functions from what I've encountered. My impression is that Workbench is just relaying the query to the database, so the function usage is limited by the local database system and version.