How to fix the mismatched input for create function synapse sql

Question

I am trying to run the code below but facing error:

Error: mismatched input '(' expecting {'.', 'AS', '-'}(line 2, pos 30)

== SQL ==

-- Create a user-defined function to parse a URL

CREATE FUNCTION dbo.fnParseURL(@URL AS VARCHAR(500), @Part AS VARCHAR(10))

     ------------------------------^^^ RETURNS VARCHAR(500) AS

%%sql
-- Create a user-defined function to parse a URL
CREATE FUNCTION dbo.fnParseURL(@URL AS VARCHAR(500), @Part AS VARCHAR(10))
RETURNS VARCHAR(500)
AS
(
BEGIN
  -- Declare variables
  DECLARE @Protocol VARCHAR(10),
  @Domain VARCHAR(100)
  DECLARE @Port VARCHAR(10)
  DECLARE @Path VARCHAR(200)
  DECLARE @Query VARCHAR(200)

  -- Check if the URL starts with http:// or https://
  IF LEFT(@URL, 7) = 'http://'
  BEGIN
    SET @Protocol = 'http'
    SET @URL = SUBSTRING(@URL, 8, LEN(@URL) - 7)
  END
  ELSE IF LEFT(@URL, 11) = 'http://www.'
  BEGIN
    SET @Protocol = 'httpw'
    SET @URL = SUBSTRING(@URL, 12, LEN(@URL) - 11)
  END
  ELSE IF LEFT(@URL, 8) = 'https://'
  BEGIN
    SET @Protocol = 'https'
    SET @URL = SUBSTRING(@URL, 9, LEN(@URL) - 8)
  END
  ELSE IF LEFT(@URL, 12) = 'https://www.'
  BEGIN
    SET @Protocol = 'httpsw'
    SET @URL = SUBSTRING(@URL, 13, LEN(@URL) - 12)
  END


  -- Find the position of the first / after the protocol
  DECLARE @SlashPos INT = CHARINDEX('/', @URL)

  -- If there is no /, then the URL has no path or query
  IF @SlashPos = 0
  BEGIN
    SET @Path = ''
    SET @Query = ''
    SET @SlashPos = LEN(@URL) + 1
  END

  -- Find the position of the first : before the first /
  DECLARE @ColonPos INT = CHARINDEX(':', @URL, 1)

  -- If there is no :, then the URL has no port
  IF @ColonPos = 0 OR @ColonPos > @SlashPos
  BEGIN
    SET @Port = ''
    SET @ColonPos = @SlashPos
  END

  -- Extract the domain from the URL
  SET @Domain = SUBSTRING(@URL, 1, @ColonPos - 1)

  -- Extract the port from the URL if it exists
  IF @Port <> ''
    SET @Port = SUBSTRING(@URL, @ColonPos + 1, @SlashPos - @ColonPos - 1)

  -- Extract the path from the URL if it exists
  IF @Path <> ''
    SET @Path = SUBSTRING(@URL, @SlashPos, LEN(@URL) - @SlashPos + 1)

  -- Find the position of the first ? in the path
  DECLARE @QuestionPos INT = CHARINDEX('?', @Path)

  -- If there is a ?, then the path has a query
  IF @QuestionPos > 0
  BEGIN
    SET @Query = SUBSTRING(@Path, @QuestionPos + 1, LEN(@Path) - @QuestionPos)
    SET @Path = SUBSTRING(@Path, 1, @QuestionPos -1)
    
    -- Replace & with ; in the query for readability
    SET @Query = REPLACE(@Query, '&', ';')
    
    -- Add brackets around each query parameter for clarity
    SET @Query = '[' + REPLACE(@Query, ';', '];[') + ']'
    
    -- Add a question mark before the query for consistency
    SET @Query = '?' + @Query
    
    -- Remove any trailing semicolons or brackets from the query
    WHILE RIGHT(@Query,1) IN (';',']')
      SET @Query = LEFT(@Query, LEN(@Query) -1)
    
    -- Remove any leading semicolons or brackets from the query
    WHILE LEFT(@Query,1) IN (';','[')
      SET @Query = RIGHT(@Query, LEN(@Query) -1)
    
    -- Add a space after each semicolon in the query for readability
    SET @Query = REPLACE(@Query, ';', '; ')
    
    -- Add a space before each question mark in the query for readability
    SET @Query = REPLACE(@Query, '?', '? ')
    
    -- Add a space after each question mark in the query for readability
    SET @Query = REPLACE(@Query, '?', '? ')
    
    -- Remove any double spaces in the query 
    WHILE CHARINDEX(' ',@Query) >0 
      SET@Query=REPLACE (@Query,' ',' ')
      
    END

   -- Return the desired part of the URL based on the input parameter
   
   IF UPPER (@Part)='PROTOCOL' RETURN(@Protocol)
   ELSE IF UPPER (@Part)='DOMAIN' RETURN(@Domain)
   ELSE IF UPPER (@Part)='PORT' RETURN(@Port)
   ELSE IF UPPER (@Part)='PATH' RETURN(@Path)
   ELSE IF UPPER (@Part)='QUERY' RETURN(@Query)
   ELSE RETURN NULL
   
END);
GO
-- Test the function with some sample URLs
SELECT dbo.fn_ParseURL('http://www.mssqltips.com/sqlservertip/1156/opportunities-with-sql-server-2005-express-edition/', 'DOMAIN') AS Domain

distinct TPID, URL,Segment,SegmentGroup,SubsidiaryName,AreaName,MSSalesAccountName,HQ_DS,
lower(URL) as URL_low,dbo.fn_ParseURL(URL,'DOMAIN') as Domain 
from TPID_URL_Mapping_Logicapp where Final_Tpid_Flag = 1;

Answer 1

Your code does not have a proper structure for the CREATE FUNCTION block. The function body should be wrapped inside a BEGIN ... END block after AS.

The final ); after END is not needed in SQL Server syntax for function creation. Replace it with just END.

-- Create a user-defined function to parse a URL

CREATE FUNCTION dbo.fnParseURL(@URL AS VARCHAR(500), @Part AS VARCHAR(10))

RETURNS VARCHAR(500)

AS

BEGIN

  -- Declare variables

  DECLARE @Protocol VARCHAR(10),

  @Domain VARCHAR(100),

  @Port VARCHAR(10),

  @Path VARCHAR(200),

  @Query VARCHAR(200)

  -- Your existing logic to parse URL...

  -- Return the desired part of the URL based on the input parameter

  IF UPPER (@Part)='PROTOCOL' RETURN(@Protocol)

  ELSE IF UPPER (@Part)='DOMAIN' RETURN(@Domain)

  ELSE IF UPPER (@Part)='PORT' RETURN(@Port)

  ELSE IF UPPER (@Part)='PATH' RETURN(@Path)

  ELSE IF UPPER (@Part)='QUERY' RETURN(@Query)

  ELSE RETURN NULL

   

END

GO