Skip to content

    • Workbench

    Workbench

    Unsupported SQL Functions in Workbench

    Member
    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.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Answers

    • Coach

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

      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Member

      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:

      image.png

      Result in Workbench:

      image.png


      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.

    • Coach

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

      1. SELECT DISTINCT MarketingClubLevel,
      2.  
      3. CASE WHEN CHARINDEX(' ',MarketingClubLevel,1) >= 0 THEN MarketingClubLevel
      4.  
      5.   ELSE SUBSTRING(MarketingClubLevel,1,(CHARINDEX(' ',MarketingClubLevel,1)))
      6.  
      7.   END AS ClubDescription
      8.  
      9.   FROM vClub

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

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

      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Coach

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

      to start troubleshooting, break your query into pieces.

      1. SELECT DISTINCT MarketingClubLevel,
      2.  
      3. -- is my query identifying the space?
      4. CHARINDEX(' ',MarketingClubLevel,1) as positionOfSpace,
      5.  
      6. CASE WHEN CHARINDEX(' ',MarketingClubLevel,1) >= 0 THEN MarketingClubLevel
      7.  
      8.   ELSE SUBSTRING(MarketingClubLevel,1,(CHARINDEX(' ',MarketingClubLevel,1)))
      9.  
      10.   END AS ClubDescription
      11.  
      12.   FROM vClub


      Jae Wilson
      Check out my 🎥 Domo Training YouTube Channel 👨‍💻

      **Say "Thanks" by clicking the ❤️ in the post that helped you.
      **Please mark the post that solves your problem by clicking on "Accept as Solution"
    • Member

      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.

      1. SELECT DISTINCT MarketingClubLevel,
      2. -- is my query identifying the space?
      3. CHARINDEX(' ',MarketingClubLevel,1) as positionOfSpace,
      4.  
      5. SUBSTRING(MarketingClubLevel,1,8) as SubstringTest, --Set an abritray end point for substring
      6.  
      7. CASE WHEN CHARINDEX(' ',MarketingClubLevel,1) >= 0 THEN MarketingClubLevel
      8.  
      9.  
      10.   ELSE SUBSTRING(MarketingClubLevel,1,(CHARINDEX(' ',MarketingClubLevel,1)))
      11.  
      12.  
      13.   END AS ClubDescription
      14.  
      15.  
      16.   FROM vClub
      17.  
      18.  

      Result:

      image.png


    • Coach

      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.

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

      rewrite it

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


      Jae Wilson
      Check out my 🎥 Domo Training YouTube Channel 👨‍💻

      **Say "Thanks" by clicking the ❤️ in the post that helped you.
      **Please mark the post that solves your problem by clicking on "Accept as Solution"
    • Member

      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.


      image.png


    • Coach

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

      Jae Wilson
      Check out my 🎥 Domo Training YouTube Channel 👨‍💻

      **Say "Thanks" by clicking the ❤️ in the post that helped you.
      **Please mark the post that solves your problem by clicking on "Accept as Solution"
    • Contributor

      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.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories