Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

TSql Function in Sql Server

Posted on 2012-03-22
24
430 Views
Last Modified: 2012-03-22
Dear Experts

I am using SQL Server 2008. I am writing a SQL Server function which cleans source system data for the target system. I am stuck. The following is some sample data:

Source Data:
29 30 31 32 39 42
9 37
9,12,19,21
6,7&20
1 3 5 30 31
13 Test Group
11 + 19
7,8,9
1-5 19-21 26-30
4 5 6-8 10 11 13-15 43
1,2,3,4
12,9 & 21

Cleaned Function Data:

29,30,31,32,39,42
09,37
09,12,19,21
06,07,20
01,03,05,30,31
Invalid
11,19
07,08,07
01,02,03,04,05,19,20,21,26,27,28,29,30
04,05,06,08,10,11,13,14,15,43
01,02,03,04
12,09,21

This is my initial attempt and I am stuck. Please help:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Classes]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_Classes]
GO
CREATE FUNCTION [dbo].[fn_Classes]
      (
            @Class      varchar(200)                  -- The Class being interepeted
      )
Returns nvarchar(254)
AS
      Begin
            DECLARE @FinalClass            NVARCHAR(254)                  -- Inprotech Class

      SELECT  @FinalClass =  CASE when (PATINDEX('%[a-zA-Z]%',rtrim(@Class))   = 0)
                                                      and (PATINDEX('%-%',rtrim(@Class)) = 0 )                  
                                                                  THEN  replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(rtrim(@Class),'&',','),'+',','),' ',','),'.',','),'and',','),'/',','),';',','),'*',''),'-',','),',,,',','),',,',',')    -- remove '&',' ','.','*','-'  and replace with ','                  -- e.g. Non Range values
                                                when (PATINDEX('%[a-zA-Z]%',rtrim(@Class))   = 0)
                                                      and (PATINDEX('%-%',rtrim(@Class)) > 0 )                  THEN  ' Range'                        -- Range values e.g. 1-5 19-21 26-30
                                                when (PATINDEX('%[a-zA-Z]%',rtrim(@Class))   > 0)      THEN  'Invalid Class'  -- e.g INT.CLASS 12 (CLASS 19)
                                          END
                                                
      
      RETURN @FinalClass      
      
      END
go
0
Comment
Question by:Delboy_UK
  • 13
  • 9
  • 2
24 Comments
 
LVL 4

Expert Comment

by:petr_hlucin
ID: 37752739
Could you please describe the exact problem? I can't see it on the first sight.
0
 

Author Comment

by:Delboy_UK
ID: 37752825
The issue is that I will need to parse the Range values e.g. 1-5 19-21 26-30 and I am not sure how to do that.
0
 

Author Comment

by:Delboy_UK
ID: 37752842
I also need to make the Function future proof. Thus, if I get the following for an example: '1*5 6-8 12 13) the function will automatically work it out to (01,05,06,07,08,12,13)
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 4

Expert Comment

by:petr_hlucin
ID: 37752960
Use the following function to convert range like 6-8 to values like 06,07,08:
CREATE FUNCTION [dbo].[fn_Range_To_Values]
      (
            @Range      varchar(200)
      )
Returns nvarchar(254)
AS
      Begin
            DECLARE @lowerBound INT
            DECLARE @upperBound INT
            DECLARE @dashIndex INT
            DECLARE @value INT
            DECLARE @result NVARCHAR
            
            SELECT @dashIndex = CHARINDEX($Range, '-')
            
            SELECT @lowerBound CAST(SUBSTRING(@Range, 1, @dashIndex - 1) AS INT)
            SELECT @upperBound CAST(SUBSTRING(@Range, @dashIndex + 1, LEN(@Range) - @dashIndex - 1) AS INT)

            SELECT @value = @lowerBound
            SELECT @result = ''
            
            WHILE @value <= @upperBound
            BEGIN
               IF @value < 10 BEGIN
                  SELECT @result = @result + ',0' + CAST(@value AS VARCHAR)
               ELSE
                  SELECT @result = @result + ',' + CAST(@value AS VARCHAR)
               END
               SELECT @value = @value + 1 
            END
      
      END
go

Open in new window

0
 

Author Comment

by:Delboy_UK
ID: 37753115
Thanks for your prompt response:

got the following errors running your function:

Msg 126, Level 15, State 1, Procedure fn_Range_To_Values, Line 16
Invalid pseudocolumn "$Range".
Msg 102, Level 15, State 1, Procedure fn_Range_To_Values, Line 18
Incorrect syntax near 'SUBSTRING'.
Msg 102, Level 15, State 1, Procedure fn_Range_To_Values, Line 19
Incorrect syntax near 'SUBSTRING'.
Msg 156, Level 15, State 1, Procedure fn_Range_To_Values, Line 28
Incorrect syntax near the keyword 'ELSE'.


editted it to the following:

CREATE FUNCTION [dbo].[fn_Range_To_Values]
      (
            @Range      varchar(200)
      )
Returns nvarchar(254)
AS
      Begin
            DECLARE @lowerBound INT
            DECLARE @upperBound INT
            DECLARE @dashIndex INT
            DECLARE @value INT
            DECLARE @result NVARCHAR
           
            SELECT @dashIndex = CHARINDEX(@Range, '-')
           
            SELECT @lowerBound = CAST(SUBSTRING(@Range, 1, @dashIndex - 1) AS INT)
            SELECT @upperBound = CAST(SUBSTRING(@Range, @dashIndex + 1, LEN(@Range) - @dashIndex - 1) AS INT)

            SELECT @value = @lowerBound
            SELECT @result = ''
           
            WHILE @value <= @upperBound
            BEGIN
               IF @value < 10
             
                  SELECT @result = @result + ',0' + CAST(@value AS VARCHAR)
               ELSE
                  SELECT @result = @result + ',' + CAST(@value AS VARCHAR)
             
               SELECT @value = @value + 1
            END
RETURN @result
      END
--go


--
i run the following:

Select dbo.[fn_Range_To_Values]('1-17 19-24 26-34')

 and got this error:

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37753149
CREATE FUNCTION [dbo].[fn_Classes]
      (
            @Class      varchar(200)                  -- The Class being interepeted
      )
Returns nvarchar(254)
AS
    Begin
   
      DECLARE @FinalClass            NVARCHAR(254)                  -- Inprotech Class
      DECLARE @Byte                  INT
      DECLARE @Class_Found           VARCHAR(200)
      DECLARE @Range_Indicator       TINYINT                        -- 0 = No Range; 1 = Start of Range.
      DECLARE @Range_Values          VARCHAR(200)

      IF (PATINDEX('%[a-zA-Z]%', @Class) > 0)
          SET @FinalClass = 'Invalid'
       
      ELSE
      BEGIN
          SET @Class = LTRIM(@Class)
          SET @Range_Indicator = 0
          WHILE @Class > ''
          BEGIN
              SET @Byte = PATINDEX('%[^0-9]%', @Class)
              IF @Byte = 0
                  SET @Class_Found = @Class
              ELSE
                  SET @Class_Found = LEFT(@Class, @Byte - 1)
              IF LEN(@Class_Found) < 2
                  SET @Class_Found = '0' + @Class_Found
              IF @Range_Indicator = 0
                  SET @FinalClass = COALESCE(@FinalClass + ',', '') + @Class_Found
              ELSE
              IF @Range_Indicator = 1
              BEGIN --generate all values in range              
                  SET @Range_Values = ''
                  ;WITH
                  cteDigits AS (
                      SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
                  ),
                  cteTally AS (
                      SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS range_val
                      FROM cteDigits [1s]
                      CROSS JOIN cteDigits [10s]
                      CROSS JOIN cteDigits [100s]
                  )
                  SELECT @Range_Values = (
                  SELECT TOP 100 PERCENT
                      CASE WHEN range_val < 10 THEN '0' + CAST(range_val AS char(1))
                           ELSE CAST(range_val AS varchar(3)) END + ','
                  FROM cteTally
                  WHERE
                      range_val BETWEEN RIGHT(',' + @FinalClass, PATINDEX('%[^0-9]%', REVERSE(',' + @FinalClass)) - 1) + 1
                          AND @Class_Found
                  ORDER BY
                      range_val
                  FOR XML PATH('')
                  )
                  SET @FinalClass = COALESCE(@FinalClass + ',', '') + LEFT(@Range_Values, LEN(@Range_Values) - 1)
              END --IF
              --SELECT @Class AS Class, @Byte AS Byte, @Class_Found AS Class_Found,
              --    @Range_Indicator AS Range_Ind, @FinalClass AS FinalClass
              IF @Byte = 0
                  BREAK
              IF SUBSTRING(@Class, @Byte, 1) = '-'
                  SET @Range_Indicator = 1
              ELSE
                  SET @Range_Indicator = 0
             
              SET @Byte = @Byte + PATINDEX('%[0-9]%', SUBSTRING(@Class, @Byte + 1, 200))
              SET @Class = LTRIM(SUBSTRING(@Class, @Byte, 200))
          END --WHILE
   
      END --ELSE    
     
      RETURN @FinalClass      
     
    END --FUNCTION
GO
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37753152
That's the complete function.  To test:



SELECT
    class_string,
    [dbo].[fn_Classes](class_string)
FROM (
    SELECT '29 30 31 32 39 42' AS class_string
    UNION ALL SELECT '9 37'
    UNION ALL SELECT '9,12,19,21'
    UNION ALL SELECT '6,7&20'
    UNION ALL SELECT '1 3 5 30 31'
    UNION ALL SELECT '13 Test Group'
    UNION ALL SELECT '11 + 19'
    UNION ALL SELECT '7,8,9'
    UNION ALL SELECT '1-5 19-21 26-30'
    UNION ALL SELECT '4 5 6-8 10 11 13-15 43'
    UNION ALL SELECT '1,2,3,4'
    UNION ALL SELECT '12,9 & 21'
) AS data
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37753167
I deliberately wrote the code to handle, and properly expand, input like this:

'1-5-19-21-26-30'

If you don't want that capability, the code will have to be adjusted to remove it.
0
 

Author Comment

by:Delboy_UK
ID: 37753276
Thanks. The code looks perfect. I am reviewing it to understand what it is doing. Running it against my dataset to see what it happening. I will update you shortly
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37753312
CORRECTION:  Need to add two lines in case the string ends in a non-num:

              ...as before...
              IF SUBSTRING(@Class, @Byte, 1) = '-'
                  SET @Range_Indicator = 1
              ELSE
                  SET @Range_Indicator = 0
             
              IF PATINDEX('%[0-9]%', SUBSTRING(@Class, @Byte + 1, 200)) = 0  --add this line
                  BREAK  --add this line
              ...as before...


I need to make one other correction, in case a space precedes the "-" for a range.

But overall I think the function will then be fairly solid.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37753357
CORRECTION:  To allow space(s) to precede "-" for range:

              ...as before...
              WHILE SUBSTRING(@Class, @Byte, 1) = SPACE(1)  --add this line
                  SET @Byte = @Byte + 1  --add this line
              IF SUBSTRING(@Class, @Byte, 1) = '-'
                  SET @Range_Indicator = 1
              ELSE
                  SET @Range_Indicator = 0
              ...as before...


If there are a lot spaces, it could be slightly more efficient to do the check a different way.  With few spaces, it's prob more efficient that way.  I don't see there being enough spaces often enough to make it worth different code.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37753379
OOPS.

Hold it, that last space correction caused PROBLEMS.

Redoing ... :-) .
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37753420
Had to add another condition to the WHILE:

              WHILE SUBSTRING(@Class, @Byte, 1) = SPACE(1) AND SUBSTRING(@Class, @Byte + 1, 1) NOT LIKE '[0-9]'
                  SET @Byte = @Byte + 1

Will now post the entire, corrected function.
0
 

Author Comment

by:Delboy_UK
ID: 37753426
Hi thanks. The results I am getting from running:
SELECT
    class_string,
    [dbo].[fn_Classes](class_string)
FROM (
    SELECT '29 30 31 32 39 42' AS class_string
    UNION ALL SELECT '9 37'
    UNION ALL SELECT '9,12,19,21'
    UNION ALL SELECT '6,7&20'
    UNION ALL SELECT '1 3 5 30 31'
    UNION ALL SELECT '13 Test Group'
    UNION ALL SELECT '11 + 19'
    UNION ALL SELECT '7,8,9'
    UNION ALL SELECT '1-5 19-21 26-30'
    UNION ALL SELECT '4 5 6-8 10 11 13-15 43'
    UNION ALL SELECT '1,2,3,4'
    UNION ALL SELECT '12,9 & 21'
) AS data


are different from what I expected

for example:
29 30 31 32 39 42      29,00,01,02,09,02
9 37                      09,07


and I dont want the range expanded. ie 1-3 6-8 = 01,02,03,06,08
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 37753431
ALTER FUNCTION [dbo].[fn_Classes]
      (
            @Class      varchar(200)                  -- The Class being interepeted
      )
Returns nvarchar(254)
AS
    Begin
       
      DECLARE @FinalClass            NVARCHAR(254)                  -- Inprotech Class
      DECLARE @Byte                  INT
      DECLARE @Class_Found           VARCHAR(200)
      DECLARE @Range_Indicator       TINYINT                        -- 0 = No Range; 1 = Start of Range.
      DECLARE @Range_Values          VARCHAR(200)

      IF (PATINDEX('%[a-zA-Z]%', @Class) > 0)
          SET @FinalClass = 'Invalid'
       
      ELSE
      BEGIN
          SET @Class = LTRIM(@Class)
          SET @Range_Indicator = 0
          WHILE @Class > ''
          BEGIN
              SET @Byte = PATINDEX('%[^0-9]%', @Class)
              IF @Byte = 0
                  SET @Class_Found = @Class
              ELSE
                  SET @Class_Found = LEFT(@Class, @Byte - 1)
              IF LEN(@Class_Found) < 2
                  SET @Class_Found = '0' + @Class_Found
              IF @Range_Indicator = 0
                  SET @FinalClass = COALESCE(@FinalClass + ',', '') + @Class_Found
              ELSE
              IF @Range_Indicator = 1
              BEGIN --generate all values in range              
                  SET @Range_Values = ''
                  ;WITH
                  cteDigits AS (
                      SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
                  ),
                  cteTally AS (
                      SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS range_val
                      FROM cteDigits [1s]
                      CROSS JOIN cteDigits [10s]
                      CROSS JOIN cteDigits [100s]
                  )
                  SELECT @Range_Values = (
                  SELECT TOP 100 PERCENT
                      CASE WHEN range_val < 10 THEN '0' + CAST(range_val AS char(1))
                           ELSE CAST(range_val AS varchar(3)) END + ','
                  FROM cteTally
                  WHERE
                      range_val BETWEEN RIGHT(',' + @FinalClass, PATINDEX('%[^0-9]%', REVERSE(',' + @FinalClass)) - 1) + 1
                          AND @Class_Found
                  ORDER BY
                      range_val
                  FOR XML PATH('')
                  )
                  SET @FinalClass = COALESCE(@FinalClass + ',', '') + LEFT(@Range_Values, LEN(@Range_Values) - 1)
              END --IF
              --SELECT @Class AS Class, @Byte AS Byte, @Class_Found AS Class_Found,
                  --@Range_Indicator AS Range_Ind, @FinalClass AS FinalClass
              IF @Byte = 0
                  BREAK
              WHILE SUBSTRING(@Class, @Byte, 1) = SPACE(1) AND SUBSTRING(@Class, @Byte + 1, 1) NOT LIKE '[0-9]'
                  SET @Byte = @Byte + 1

              IF SUBSTRING(@Class, @Byte, 1) = '-'
                  SET @Range_Indicator = 1
              ELSE
                  SET @Range_Indicator = 0
             
              IF PATINDEX('%[0-9]%', SUBSTRING(@Class, @Byte + 1, 200)) = 0
                  BREAK
              SET @Byte = @Byte + PATINDEX('%[0-9]%', SUBSTRING(@Class, @Byte + 1, 200))
              SET @Class = LTRIM(SUBSTRING(@Class, @Byte, 200))
          END --WHILE
   
      END --ELSE    
     
      RETURN @FinalClass      
     
    END --FUNCTION
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37753436
SELECT
    class_string,
    [dbo].[fn_Classes](class_string)
FROM (
    SELECT '29 30 31 32 39 42' AS class_string
    UNION ALL SELECT '9 37'
    UNION ALL SELECT '9,12,19,21'
    UNION ALL SELECT '6,7&20'
    UNION ALL SELECT '1 3 5 30 31'
    UNION ALL SELECT '13 Test Group'
    UNION ALL SELECT '11 + 19'
    UNION ALL SELECT '7,8,9'
    UNION ALL SELECT '1-5  -19 21-26 30' --<<-- note spaces before "-"
    UNION ALL SELECT '4 5 6-8 10 11 13-15 43'
    UNION ALL SELECT '1,2,3,4'
    UNION ALL SELECT '12,9 & 21'
    UNION ALL SELECT '1*5 6-8 12 13)' --<<-- ends in non-numeric
) AS data
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37753447
>> Hi thanks. The results I am getting from running: <<

Yeah, SORRY about that, that was because my second / last correction caused a BIG problem.

I've corrected that in subsequent posts above.
0
 

Author Comment

by:Delboy_UK
ID: 37753467
Yep Thanks. I am testing your solution against my dataset now and will let you know shortly. Thanks for your prompt assistance
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37753515
No problem.  I really think we're good now ... until you find some other odd data pattern :-) .
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37753530
Note that the extra char(s) before a "-" must all be spaces for it to still count as a range, as coded now.

For example, these:
'5  *- 8'
'5  ,- 8'
'5  /- 8'  etc.

would NOT be considered a range.
0
 

Author Comment

by:Delboy_UK
ID: 37754482
Thanks for you assistance. I am still testing and trying to understand the script you provided and also comment it for the future. I will accept the solutuion though as it has been of great help. Thanks once again.
0
 

Author Closing Comment

by:Delboy_UK
ID: 37754492
Brilliant solution
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37754520
Thanks.

Sorry, I really don't have time to comment it myself now.

If you'd like, you can follow up with any qs, of course, on why the code is as it is.
0
 

Author Comment

by:Delboy_UK
ID: 37754532
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question