SQL Cheat Sheet

Date Conversion

Strip Non-Alphabetic Characters

To strip all non-alphabetic characters from string in SQL Server

Create a scalar function

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Sample Usage

--sample input   : '1-_ @ MARCH"'''''
--expected output: MARCH
SELECT dbo.RemoveNonAlphaCharacters('1-_ @ MARCH"''''')

Validate UEN

A custom scalar function created by myself to validate for Unique Entity Number (UEN). *PS Feel free to contribute for the UEN validate function if there is any missing validation rules.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ================================================
-- Scalar function to validate Unique Entity Number (UEN)
-- Please note that this function did not 
-- validate entity type for (C) All entities which are issued new UEN 
-- ================================================
-- # UEN Types
-- (A) Businesses registered with ACRA before 1 January 2009 - Foramt: nnnnnnnnX (9 digits)
-- (B) Local companies registered with ACRA before 1 January 2009 - Foramt: yyyynnnnnX (10 digits)
-- (C) All entities which are issued new UEN - Foramt: TyyPQnnnnX / SyyPQnnnnX / RyyPQnnnnX (10 digits)
-- ================================================
-- # Descriptions
-- ‘n’ = a number 
-- ‘P’= an alphabetical letter
-- ‘Q’ = an alpha-numeric digit
-- ‘PQ’ = Entity-type 1
-- ‘Tyy’ / ‘Syy’ / ‘yyyy’= year of issuance 2
-- ‘X’ = a check alphabetFor example, the UEN for a limited liability partnership (LLP) formed on 1 January 2009 could be ‘T09LL0001B’.
-- ================================================

CREATE FUNCTION [dbo].[ValidateUEN] (@str VARCHAR(15)) returns BIT
AS
BEGIN

    SET @str = LTRIM(RTRIM(ISNULL(@str, '')));

    -- check that uen is not empty
    IF @str = ''
        RETURN 0

    -- check if uen is 9 or 10 digits
    IF LEN(@str) < 9 OR LEN(@str) > 10
        RETURN 0

    -- transform to UPPER CASE
    SET @str = UPPER(@str);

    -- (A) Businesses registered with ACRA - nnnnnnnnX (9 digits)
    IF LEN(@str) = 9
    BEGIN
        -- check that last character is a letter/alphabet
        IF SUBSTRING(@str, 9, 1) NOT LIKE '%[A-Z]%' 
            RETURN 0

        -- check that first 8 letters are all numbers
        IF SUBSTRING(@str, 1, 8) NOT LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
            RETURN 0

        -- (A) Businesses registered with ACRA (SUCCESS)
        RETURN 1
    END
    ELSE IF LEN(@str) = 10
    BEGIN
        -- check that last character is a letter
        IF SUBSTRING(@str, 10, 1) NOT LIKE '%[A-Z]%' 
            RETURN 0

        -- (B) Local companies registered with ACRA before 1 January 2009 - yyyynnnnnX (10 digits)
        IF SUBSTRING(@str, 1, 4) LIKE ('[0-9][0-9][0-9][0-9]')
        BEGIN            
            IF SUBSTRING(@str, 5, 5) LIKE ('[0-9][0-9][0-9][0-9][0-9]')
                RETURN 1
            ELSE
                RETURN 0
        END
        ELSE
        BEGIN
            -- (C) All entities which are issued new UEN - TyyPQnnnnX / SyyPQnnnnX / RyyPQnnnnX (10 digits)

            -- check that 1st letter is either T or S or R
            IF SUBSTRING(@str, 1, 1) NOT IN ('T', 'S', 'R')
                RETURN 0

            -- check that 2nd and 3rd letters are numbers only
            IF SUBSTRING(@str, 2, 2) NOT LIKE ('[0-9][0-9]')
                RETURN 0

            -- check that 4th letter is an alphabet
            IF SUBSTRING(@str, 4, 1) NOT LIKE '%[A-Z]%' 
                RETURN 0

            -- check that 5th letter is an alphanumeric
            IF PATINDEX('%[^a-zA-Z0-9]%' , SUBSTRING(@str, 5, 1)) <> 0
                RETURN 0

            -- check that 6th to 9th letters are numbers only
            IF SUBSTRING(@str, 6, 4) NOT LIKE ('[0-9][0-9][0-9][0-9]')
                RETURN 0

            -- (C) All other entities which will be issued new UEN (SUCCESS)
            RETURN 1
        END

        RETURN 0
    END

    RETURN 0 --default false
END
GO

Sample Usage

-- expected valid (A) Businesses UEN in format 'nnnnnnnnX'
SELECT dbo.ValidateUEN('12345678X')

-- expected valid (B) Local companies in format 'yyyynnnnnX'
SELECT dbo.ValidateUEN('202012345X')

-- expected valid (C) issued new UEN in format 'TyyPQnnnnX' / 'SyyPQnnnnX' / 'RyyPQnnnnX'
SELECT dbo.ValidateUEN('T19L11990Z')
SELECT dbo.ValidateUEN('S19L01900A')
SELECT dbo.ValidateUEN('R19L10000B')

-- expected invalid format
SELECT dbo.ValidateUEN('1234567890X') -- exceeds 10 chars
SELECT dbo.ValidateUEN('123456ABC') --invalid format of 'nnnnnnnnX
SELECT dbo.ValidateUEN('01231234AX') --invalid format of 'yyyynnnnnX
SELECT dbo.ValidateUEN('A19L11990Z') --invalid format of 'TyyPQnnnnX' / 'SyyPQnnnnX' / 'RyyPQnnnnX'

Reference

Validate SG NRIC

Scalar function to validate Singapore Identification Number / FIN Number.

--modified from the code reference: https://gist.github.com/muraray/313482873deaf33250f20286ff8192fe

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Murali, scanmurali@gmail.com>
-- Create date: <2018-04-28 17:30:50.723>
-- Description:    <Function validates the supplied NRIC string>

-- Examples NRIC 
-- Series - S | DOB - Any => S3845529Z | S6432041F | S0956648A
-- Series - F | DOB - Any => F0867553T | F0407353M | F6271257X
-- Series - G | DOB - Any => G8775017K | G8747022P | G9268281T
-- Series - T | DOB - 2010 -> 2019 => T1062864J | T1628028Z | T1503187A
-- Series - T | DOB - 2000 -> 2009 => T0143822G | T0284137H | T0320799J
-- =============================================
CREATE FUNCTION [dbo].[ValidateNRIC] (@str VARCHAR(9)) returns BIT
AS
BEGIN

    DECLARE @icArray TABLE  ( item INT)
    DECLARE @weight  INT
    DECLARE @offset  INT
    DECLARE @icArray_0 VARCHAR(1)
    DECLARE @icArray_8 VARCHAR(1)
    DECLARE @temp INT
    DECLARE @st TABLE (rowid INT, value VARCHAR(1))
    DECLARE @fg TABLE (rowid INT, value VARCHAR(1))
    DECLARE @theAlpha VARCHAR
    DECLARE @returnValue BIT = 0

    IF(LEN(@str) = 9)
    BEGIN

        --INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 2, 1) as INT) * 2
        --INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 3, 1) as INT) * 7
        --INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 4, 1) as INT) * 6
        --INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 5, 1) as INT) * 5
        --INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 6, 1) as INT) * 4
        --INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 7, 1) as INT) * 3
        --INSERT INTO @icArray SELECT CAST(SUBSTRING(@str, 8, 1) as INT) * 2

        INSERT INTO @icArray SELECT CASE WHEN ISNUMERIC(SUBSTRING(@str, 2, 1)) = 1 THEN CAST(SUBSTRING(@str, 2, 1) as INT) * 2 ELSE NULL END
        INSERT INTO @icArray SELECT CASE WHEN ISNUMERIC(SUBSTRING(@str, 3, 1)) = 1 THEN CAST(SUBSTRING(@str, 3, 1) as INT) * 7 ELSE NULL END
        INSERT INTO @icArray SELECT CASE WHEN ISNUMERIC(SUBSTRING(@str, 4, 1)) = 1 THEN CAST(SUBSTRING(@str, 4, 1) as INT) * 6 ELSE NULL END
        INSERT INTO @icArray SELECT CASE WHEN ISNUMERIC(SUBSTRING(@str, 5, 1)) = 1 THEN CAST(SUBSTRING(@str, 5, 1) as INT) * 5 ELSE NULL END
        INSERT INTO @icArray SELECT CASE WHEN ISNUMERIC(SUBSTRING(@str, 6, 1)) = 1 THEN CAST(SUBSTRING(@str, 6, 1) as INT) * 4 ELSE NULL END
        INSERT INTO @icArray SELECT CASE WHEN ISNUMERIC(SUBSTRING(@str, 7, 1)) = 1 THEN CAST(SUBSTRING(@str, 7, 1) as INT) * 3 ELSE NULL END
        INSERT INTO @icArray SELECT CASE WHEN ISNUMERIC(SUBSTRING(@str, 8, 1)) = 1 THEN CAST(SUBSTRING(@str, 8, 1) as INT) * 2 ELSE NULL END

        IF EXISTS(SELECT 1 FROM @icArray WHERE item IS NULL)
        BEGIN
            --PRINT 'invalid numeric of 2nd-8th characters'
            SET @returnValue = 0
        END
        ELSE
        BEGIN
            SELECT @weight = SUM(item) FROM @icArray

            SELECT @icArray_0 = SUBSTRING(@str, 1, 1), @icArray_8 = SUBSTRING(@str, 9, 1)

            IF(@icArray_0 = 'T' OR @icArray_0 = 'G') SET @offset = 4 ELSE SET @offset = 0
            SET @temp = (@offset + @weight) % 11;

            INSERT INTO @st SELECT'0','J'
            INSERT INTO @st SELECT'1','Z'
            INSERT INTO @st SELECT'2','I'
            INSERT INTO @st SELECT'3','H'
            INSERT INTO @st SELECT'4','G'
            INSERT INTO @st SELECT'5','F'
            INSERT INTO @st SELECT'6','E'
            INSERT INTO @st SELECT'7','D'
            INSERT INTO @st SELECT'8','C'
            INSERT INTO @st SELECT'9','B'
            INSERT INTO @st SELECT'10','A'

            INSERT INTO @fg SELECT '0', 'X'
            INSERT INTO @fg SELECT '1', 'W'
            INSERT INTO @fg SELECT '2', 'U'
            INSERT INTO @fg SELECT '3', 'T'
            INSERT INTO @fg SELECT '4', 'R'
            INSERT INTO @fg SELECT '5', 'Q'
            INSERT INTO @fg SELECT '6', 'P'
            INSERT INTO @fg SELECT '7', 'N'
            INSERT INTO @fg SELECT '8', 'M'
            INSERT INTO @fg SELECT '9', 'L'
            INSERT INTO @fg SELECT '10', 'K'

            IF (@icArray_0 = 'S' OR @icArray_0 =  'T') 
                SELECT @theAlpha = Value from @st where rowid = @temp;
            ELSE IF (@icArray_0 =  'F' OR @icArray_0 =  'G')
                SELECT @theAlpha = Value from @fg where rowid = @temp; 

            -- SELECT @theAlpha, @temp
            If(@icArray_8 = @theAlpha) SET @returnValue = 1

            --SELECT @returnValue
        END

    END
    RETURN @returnValue
END
GO

Sample Usage

--expected valid
SELECT dbo.ValidateNRIC(CAST('S8888888Z' AS varchar(9))) 

--expected invalid
SELECT dbo.ValidateNRIC(CAST('S8888888J' AS varchar(9)))

PreviousSession State NextExport Pipe Delimited CSV With cmdshell

Last updated 4 years ago