SQL SERVER Get Numeric Value From Alpha Numeric String

SQL SERVER – Get Numeric Value From Alpha Numeric String – UDF for Get Numeric Numbers Only and it was very handy tool for me. This post has provided Stored Procedure and the same can be easily converted to Function.

CREATE PROCEDURE [dbo].[CleanDataFromAlphaNumeriString]
@alpha VARCHAR(50),
@decimal DECIMAL(14, 5) OUTPUT
AS BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMsg VARCHAR(50)
DECLARE @Pos INT
DECLARE @CommaPos INT
DECLARE @ZeroExists INT
DECLARE @alphaReverse VARCHAR(50)
DECLARE @NumPos INT
DECLARE @Len INT
-- 1 Reverse the alpha in order to get the last position of a numeric value
SET @alphaReverse = REVERSE(@alpha)
-- 2 Get the last position of a numeric figure
SET @NumPos = PATINDEX('%[0-9]%', @alphaReverse)
-- 3 Get the lenght of the string
SET @Len = LEN(@alpha)
-- 4 Add a comma after the numeric data in case it's no decimal number
SET @alpha = SUBSTRING(@alpha, 1, (@Len - @NumPos + 1))
+ ','
+ SUBSTRING(@alpha, (@Len - @NumPos + 2), 50)
-- Check if there is a zero (0) in the @alpha, then we later set the @decimal to 0
-- if it's 0 after the handling, else we set @decimal to NULL
-- If 0 no match, else there is a match
SET @ZeroExists = CHARINDEX ( '0' , @alpha ,1 )
-- Find position of , (comma)
SET @CommaPos = 1
SET @CommaPos = PATINDEX('%,%', @alpha)
IF (@CommaPos = '') BEGIN
SET @CommaPos = 20
END
SET @Pos = PATINDEX('%[^0-9]%',@alpha)
-- Replaces any aplha with '0' since we otherwice can't keep track of where the decimal
-- should be put in. We assume the numeric number has no aplhe inside. The regular way
-- to solve this is to replace with ”, but then we miss the way to find the place to
-- put in the decimal.
WHILE (@Pos > 0) BEGIN
SET @alpha = STUFF(@alpha, @pos, 1, '0')
SET @Pos = PATINDEX('%[^0-9]%',@alpha)
END
IF (@alpha IS NOT NULL AND @alpha != '') BEGIN
SET @decimal = CONVERT(DECIMAL(14, 5), SUBSTRING(@alpha, 1, (@CommaPos - 1))
+ '.'
+ SUBSTRING(@alpha, (@CommaPos + 1), 20))
END
-- Since we in this case don't want to set 0 if where is no numeric value, we set NULL to be safe
IF (@decimal = 0 AND @ZeroExists = 0) BEGIN
SET @decimal = NULL
END
END
GO

If you run above SP as shown below it will work
DECLARE @myRetVal DECIMAL(14,5)
EXEC [CleanDataFromAlphaNumeriString] 'ABC355,88ghf', @myRetVal OUTPUT
SELECT @myRetVal ReturnValue

ReturnValue
---------------------------------------
355.88000

Read more »

SQL Server Identify missing indexes

In this post you will see how to identify missing indexes

SELECT DISTINCT DB_NAME(Database_ID) [Database]
,OBJECT_NAME(Object_ID) [Table]
,Equality_Columns
,Included_Columns
FROM sys.dm_db_missing_index_details mid
WHERE Database_ID = DB_ID()
ORDER BY 2

Read more »

SQL Server Get foreign keys of a table

In this post you will learn how to get foriegn keys of a table in SQl Server

Here T-SQl for fetching foriegn keys of a table.


SELECT f.name AS ForeignKey
,OBJECT_NAME(f.parent_object_id) AS TableName
,COL_NAME(fc.parent_object_id
,fc.parent_column_id) AS ColumnName
,OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName
,COL_NAME(fc.referenced_object_id
,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME (f.referenced_object_id) = 'TableName'
ORDER BY 2

Read more »

SQL Server Cursor Example

DECLARE @String nVARCHAR(MAX)
DECLARE @getInputBuffer CURSOR
SET @getInputBuffer = CURSOR FOR
SELECT Text FROM Table

OPEN @getInputBuffer

FETCH NEXT
FROM @getInputBuffer INTO @String

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @String
FETCH NEXT
FROM @getInputBuffer INTO @String

END

CLOSE @getInputBuffer
DEALLOCATE @getInputBuffer

Read more »

SQL Server Using DATETIME column

In production environment we have seen that there are certain times when you make a good query by applying best practices but you don’t get good results at the time of sorting.
This might happen because of DATETIME columns which are mostly used for sorting purpose and indexes for these columns do not give you appropriate results.
Mentioned below is tip for using DATETIME column which can enhance the speed of your query while maintaining same number of indexes.
This technique requires mentioned below changes in your table: -

One Persisted Computed Column (BIGINT) which will store DATETIME in integer format by removing special symbols from DATETIME
This computed column will be added in the covering index
Query will use this column instead of DATETIME column




Example :
/* Create Sample Table */
CREATE TABLE [dbo].[MyTable](
[C1] [int] IDENTITY(1,1) NOT NULL,
[C2] [DATETIME] NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[C1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/* Data Insert */
SET IDENTITY_INSERT [dbo].[MyTable] ON
INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (1, CAST(0x00009E5E00000000 AS DATETIME))
INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (2, CAST(0x00009E5F00000000 AS DATETIME))
INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (3, CAST(0x00009E6000000000 AS DATETIME))
INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (4, CAST(0x00009E6100000000 AS DATETIME))
INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (5, CAST(0x00009E7D00000000 AS DATETIME))
INSERT [dbo].[MyTable] ([C1], [C2]) VALUES (6, CAST(0x00009E7E00000000 AS DATETIME))
SET IDENTITY_INSERT [dbo].[MyTable] OFF

/* Query */
DECLARE @D1 DATETIME
DECLARE @D2 DATETIME
SET @D1 = GETDATE()-10
SET @D2 = GETDATE()
SELECT C1
,C2
FROM MyTable
WHERE C2 BETWEEN @D1 AND @D2

/* Change in Table Add New Column that will store DATETIME in Integer Format */
ALTER TABLE MyTable
ADD [C3] AS (CONVERT([bigint],CONVERT([varchar],[C2],(112))+replace(CONVERT([varchar],[C2],(114)),':',''),0)) PERSISTED

/* Existing Index for query */
CREATE NONCLUSTERED INDEX [Index_For_C2] ON MyTable
(
[C2] ASC,
[C1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/* Add New Column to Existing Index for query */
CREATE NONCLUSTERED INDEX [Index_For_C2] ON MyTable
(
[C3] ASC,
[C2] ASC,
[C1] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/*Updated Query using Integer Column instead of DATETIME having same result set*/
DECLARE @D1 BIGINT
DECLARE @D2 BIGINT
SET @D1 = (CONVERT([bigint],CONVERT([varchar],GETDATE()-10,(112))+replace(CONVERT([varchar],GETDATE()-10,(114)),':',''),0))
SET @D2 = (CONVERT([bigint],CONVERT([varchar],GETDATE(),(112))+replace(CONVERT([varchar],GETDATE(),(114)),':',''),0))
SELECT C1
,C2
FROM MyTable
WHERE C3 BETWEEN @D1 AND @D2


The above query will now use the new updated index and will be much faster as it will get records by using an integer column instead of DATETIME column.

Read more »

SQL Server Validate Email Address

AT times working with email addresses to validate email address in sql server becomes difficult so here is a User Defined function to validaet email address. You can use to validate the email address

It will validate mentioned below list

No Spaces are allowed
email cant start with '@'
email cant end at '.'
@must be in email and only once
domain name should be at the end and must be at least two characters
email cant have patterns like '..' or '.@'

CREATE FUNCTION [dbo].[validateEmailAddress]
(
@EmailAddress nVARCHAR(4000)
)
RETURNS TINYINT AS
BEGIN


DECLARE @Result TINYINT
SELECT @Result =
CASE WHEN
CHARINDEX(' ',LTRIM(RTRIM(@EmailAddress))) = 0
AND LEFT(LTRIM(@EmailAddress),1) <> '@'
AND RIGHT(RTRIM(@EmailAddress),1) <> '.'
AND CHARINDEX('.',@EmailAddress,CHARINDEX('@',@EmailAddress)) - CHARINDEX('@',@EmailAddress) > 1
AND LEN(LTRIM(RTRIM(@EmailAddress))) - LEN(REPLACE(LTRIM(RTRIM(@EmailAddress)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@EmailAddress)))) >= 3
AND (CHARINDEX('.@',@EmailAddress) = 0 AND CHARINDEX('..',@EmailAddress) = 0)
THEN 1 ELSE 0 END

RETURN @Result

END



Example:-

it will return 1 for correct email address and 0 for incorrect
:
SELECT dbo.validateEmailAddress('myemail@mydomain.com') -- Correct

SELECT dbo.validateEmailAddress('@myemail@m domain.com') -- Incorrect

Read more »

how to pass column name with parameter in insert sql statment

DECLARE @Value nvarchar(100)
DECLARE @InsertString nvarchar(1000)
DECLARE @name sysname

SET @name ='name'
SET @Value = 'a'

SET @InsertString= 'INSERT INTO EMPLOYEES (id,' + @name + ') values(1, @Value)'


EXEC sp_executesql @InsertString, N'@Value nvarchar(100)', @Value 

Read more »

SQL Calculate Days in Month

There is no option available in SQL to calculate days of a specific month, so it is very poor approach to write CASE statement in every single T-SQL.

USER DEFINED FUNCTION is the right way of doing these kind of tasks which have to be done on regular basis.

CALL:
select dbo.GetDaysinmonth(5)

OUTPUT:
31

T-SQL for Calculate Days in Month Function

USE [TESTDB]
GO
/****** Object:  UserDefinedFunction [dbo].[GetDaysInMonth]    Date: 01/31/2012 16:04:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetDaysInMonth] ( @pDate    DATETIME )
RETURNS INT
AS
BEGIN

    RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
                WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
                ELSE CASE WHEN (YEAR(@pDate) % 4    = 0 AND
                                YEAR(@pDate) % 100 != 0) OR
                               (YEAR(@pDate) % 400  = 0)
                          THEN 29
                          ELSE 28
                     END
           END

END


Enjoy!!

Read more »

SQL PROPER CASE Function

Guys many times we need to use proper casing for our website visitors and we need some method which can do proper casing for us. In SQL there is no System Function for this job. So we have to make our own function to do Proper Case for us.

Below is the syntax for "PROPER CASE" function and before that is the method to call SQL User Defined Functions

How to call:

SELECT dbo.[PROPERCASE]('This is test to check Proper case function')

OUTPUT:
This Is Test To Check Proper Case Function


T-SQL for PROPER CASE function



USE [TESTDB]
GO
/****** Object:  UserDefinedFunction [dbo].[PROPERCASE]    Script Date: 01/31/2012 15:30:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[PROPERCASE]
(
--The string to be converted to proper case
@input varchar(8000)
)
--This function returns the proper case string of varchar type
RETURNS varchar(8000)
AS
BEGIN
    IF @input IS NULL
    BEGIN
        --Just return NULL if input string is NULL
        RETURN NULL
    END
   
    --Character variable declarations
    DECLARE @output varchar(8000)
    --Integer variable declarations
    DECLARE @ctr int, @len int, @found_at int
    --Constant declarations
    DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int
   
    --Variable/Constant initializations
    SET @ctr = 1
    SET @len = LEN(@input)
    SET @output = ''
    SET @LOWER_CASE_a = 97
    SET @LOWER_CASE_z = 122
    SET @Delimiter = ' ,-'
    SET @UPPER_CASE_A = 65
    SET @UPPER_CASE_Z = 90
   
    WHILE @ctr <= @len
    BEGIN
        --This loop will take care of reccuring white spaces
        WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
        BEGIN
            SET @output = @output + SUBSTRING(@input,@ctr,1)
            SET @ctr = @ctr + 1
        END

        IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
        BEGIN
            --Converting the first character to upper case
            SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
        END
        ELSE
        BEGIN
            SET @output = @output + SUBSTRING(@input,@ctr,1)
        END
       
        SET @ctr = @ctr + 1

        WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
        BEGIN
            IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
            BEGIN
                SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
            END
            ELSE
            BEGIN
                SET @output = @output + SUBSTRING(@input,@ctr,1)
            END
            SET @ctr = @ctr + 1
        END
       
    END
RETURN @output
END

This is it guys enjoy.

Read more »

SQL Datetime Format

Let's take a look at the Datetime format of SQL, Below is the T-SQL How SQL deals with datetime.

Format	Query
USA mm/dd/yy	select convert(varchar, getdate(), 1)
ANSI yy.mm.dd	select convert(varchar, getdate(), 2)
British/French dd/mm/yy	select convert(varchar, getdate(), 3)
German dd.mm.yy	select convert(varchar, getdate(), 4)
Italian dd-mm-yy	select convert(varchar, getdate(), 5)
dd mon yy	select convert(varchar, getdate(), 6)
Mon dd, yy	select convert(varchar, getdate(), 7)
USA mm-dd-yy	select convert(varchar, getdate(), 10)
JAPAN yy/mm/dd	select convert(varchar, getdate(), 11)
ISO yymmdd	select convert(varchar, getdate(), 12)
mon dd yyyy hh:miAM (or PM)	select convert(varchar, getdate(), 100)
mm/dd/yyyy	select convert(varchar, getdate(), 101)
yyyy.mm.dd	select convert(varchar, getdate(), 102)
dd/mm/yyyy	select convert(varchar, getdate(), 103)
dd.mm.yyyy	select convert(varchar, getdate(), 104)
dd-mm-yyyy	select convert(varchar, getdate(), 105)
dd mon yyyy	select convert(varchar, getdate(), 106)
Mon dd, yyyy	select convert(varchar, getdate(), 107)
hh:mm:ss	select convert(varchar, getdate(), 108)
Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)	select convert(varchar, getdate(), 109)
mm-dd-yyyy	select convert(varchar, getdate(), 110)
yyyy/mm/dd	select convert(varchar, getdate(), 111)
yyyymmdd	select convert(varchar, getdate(), 112)
Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)	select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13)
hh:mi:ss:mmm(24h)	select convert(varchar, getdate(), 114)

Read more »

SQL Case Syntax

Here is simple way to using case in your T-SQL

select

CASE
when count(accounts) < 1 then 'Incomplete'
when count(accounts) = 1 then 'One Login'
when count(accounts) = 2 then 'Two Login'
when count(accounts) = 3 then 'Three Login'
when count(accounts) > 3 then 'Complete'

END LoginInfo
from tblemplogin

Read more »

Import Bulk Data into SQL Table From CSV File or TSV File

Create Table tbl_CSVTSVTest
(
id int, name varchar(20), city varchar(20)
)

bulk
insert tbl_CSVTSVTest
from 'd:\testcsv.txt'
with
(
FieldTerminator = ',', -- for tsv file use FieldTerminator = '    '
RowTerminator = '\n'
)
go



select * from tbl_CSVTSVTest

Read more »

SQL SERVER – Difference Between Rebuild Index and Reorganize Index with T-SQL Script

Index Rebuild : This process drops the existing Index and Recreates the index.

USE TESTDB; 

GO 

ALTER INDEX ALL ON Production.Product REBUILD 

GO 

Index Reorganize : This process physically reorganizes the leaf nodes of the index. 

USE TESTDB; 

GO

ALTER INDEX ALL ON Production.Product REORGANIZE 

GO

Read more »