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 »