Remove bad characters from a string

This simple CTE can be used to remove bad characters in string. It will take string and allowed characters and returns neat string. Change the @AllowedPattern as you wish and check the output string. For example if you have a string abc@#$()123 and you want a string abc123, Set allowedPattern to '0-9a-z. With little tweak this can be converted to a function.





DECLARE @String VARCHAR(8000),
@AllowedPattern VARCHAR(100),
@Holder VARCHAR(8000)
SET @String = '!@#$% 123 ??abc##rd,,,'
SET @AllowedPattern = '0-9a-z'
SET @Holder = ''

;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
          FROM t4 x, t4 y)
SELECT @Holder = @Holder + SUBSTRING(@String,num,1)
FROM tally
WHERE num <= LEN(@String)
AND PATINDEX('['+@AllowedPattern+']',SUBSTRING(@String,num,1)) = 1

SELECT @Holder

Convert a given string into Lowercase without using LOWER..

Continuation to previous post...converting a string to lower case string

CREATE FUNCTION udfLower

(

   @InputString NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

AS

BEGIN

   DECLARE @LowerString    NVARCHAR(MAX)  = ''

   DECLARE @Counter        INT            = 1

   DECLARE @Character      CHAR           = ''

   DECLARE @AsciiValue     TINYINT

   DECLARE @NewAsciiValue  TINYINT

   WHILE (@Counter <= LEN(@InputString))

   BEGIN

      SET @Character = SUBSTRING(@InputString,@Counter,1)

      IF ASCII(@Character) BETWEEN 65 AND 90

      BEGIN

         SET @NewAsciiValue = ASCII(@Character) + 32

         SET @LowerString = @LowerString + CHAR(@newAsciiValue)

      END

      ELSE

      BEGIN

         SET @LowerString = @LowerString + @Character

      END

      SET @Counter = @Counter + 1

   END

   RETURN @LowerString

END

GO

SELECT dbo.udfLower('this is a test STRingdadas@#@#@')

Convert given string into upper case with out using builtin UPPER function

How to convert a given string into upper case string without using builtin UPPER function. Below is the code.

CREATE FUNCTION udfUpper

(

   @InputString NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

AS

BEGIN

   DECLARE @UpperString    NVARCHAR(MAX)  = ''

   DECLARE @Counter        INT            = 1

   DECLARE @Character      CHAR           = ''

   DECLARE @AsciiValue     TINYINT

   DECLARE @NewAsciiValue  TINYINT

   WHILE (@Counter <= LEN(@InputString))

   BEGIN

      SET @Character = SUBSTRING(@InputString,@Counter,1)

      IF ASCII(@Character) BETWEEN 97 AND 122

      BEGIN

         SET @NewAsciiValue = ASCII(@Character) - 32

         SET @UpperString = @UpperString + CHAR(@newAsciiValue)

      END

      ELSE

      BEGIN

         SET @UpperString = @UpperString + @Character

      END

      SET @Counter = @Counter + 1

   END

   RETURN @UpperString

END

GO

SELECT dbo.udfUpper('this is a test STRingdadas@#@#@')

Create a flat file utility

This is my first post WRT C# and/OR VB.NET. At work I was given a task to create a utility that will generate a flat file where we can pass database servername, databasename, procedurename (no parameterized procs and which spit out multiple resultsets), Output file path, output file name, fileextension, column delimiter (comma,tab,colon,semicolon,pipe), header required (Yes/no/y/n), append timestamp to filename, row terminator(unix/windows). I came up with a solution to create a console application in both c# and VB.NET.

Here is the VB.NET code. This works only with SQL Server connections

Example:

CreateFlatFile.exe Server DB Procedure FilePath FileName .csv colon y y unix. Syntax is same for C# as well.

VB code
Csharp code

Generate Random value

Wonder how random confirmation numbers are generated? Here is one way to generate. This code will exclude all the special characters and consider values from 0-9 and [a-z]/[A-Z]. But the final result will be all UPPER case characters.


 DECLARE @Lenght INT = 6
 DECLARE @OutPut VARCHAR(10) = ''
 DECLARE @ExcludeList VARCHAR(100) = '~`!@#$%^&*()_-+={[}]|\:;"''?/>.<,'
 DECLARE @CharList CHAR

 WHILE (@Lenght >0)

 BEGIN
        SET @CharList = CHAR(RAND() * 72 + 48)
        IF CHARINDEX(@CharList,@ExcludeList) = 0
        BEGIN
               SELECT @OutPut = @OutPut + @CharList
               SET @Lenght = @Lenght - 1
        END
 END

 SELECT UPPER(@OutPut)

 

 

LEAD() Function in SQL 2012 makes life easy

2012 introduced LEAD Function. Lets take a scenario for example. We have a categorytable which has categoryID and CategoryValue. You are required to show another calculated field called NextCategoryValue which is basically the value from next record. Normally what we do is write a CTE or table expression and get the next value. The output should be as follows

Here is the sample code

IF OBJECT_ID('dbo.Category','U') IS NOT NULL

DROP TABLE dbo.Category
GO
CREATE TABLE Category
(
    CategoryID INT
    ,CategoryValue INT
)
 

INSERT INTO Category(CategoryID, CategoryValue)

VALUES (1,1), (1,2), (1,4), (1,5), (2,8), (2,10), (3,11),(3,13)

--Get the next Category Value

;WITH CTE
AS
(
    SELECT CategoryID
            ,CategoryValue
           ,ROW_NUMBER() OVER(ORDER BY CategoryValue) AS Ranges
    FROM dbo.Category
)
SELECT A.CategoryID
    , A.CategoryValue
    , B.CategoryValue AS NextCategoryValue
FROM
CTE A
LEFT JOIN CTE B
ON A.Ranges + 1 = B.Ranges

We can achieve the same functionality with LEAD() function with very less code and with a huge perf. improvement

--USE LEAD() Function to get next category value

SELECT CategoryID
        ,CategoryValue
        ,LEAD(CategoryValue) OVER(ORDER BY CategoryValue) AS NextCategoryValue
FROM dbo.Category

We can also use PARTITION BY in the OVER clause of lead function if we want to get the values based on categoryID or a key value. Here is the execution plan

This is very useful for date ranges and other stuff.

Calculating the number of occurances of a character/string inside a string

How can we find number of occurances of a particular string in another string? For example in SQLServer, how can we find number of occurances of 'e'? Its a simple but interesting logic

DECLARE @String AS VARCHAR(MAX) = 'SQLServer'
,@SearchString AS VARCHAR(MAX) = 'e'

SELECT (LEN(@String) - LEN(REPLACE(@String,@SearchString,'')))/LEN(@SearchString)

So simple. First take the actual string. Replace the string with nothing by replace function and using search string and divide the whole value with the length of search string.