Find only characters or only integer from alphanumeric value

Find only characters from string :

DECLARE @Temp VARCHAR(100)='548STA123Ldfgh45df45df5446fg54645dfg546';
Declare @NumRange AS varchar(50) = '%[0-9]%';
    While PatIndex(@NumRange, @Temp) > 0
        SET @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1 ,'')

 SELECT @Temp

====================================

Find only integers from string :

DECLARE @var VARCHAR(100)='5STA123Ldfgh45df45df5446fg54645dfg546';
DECLARE @intdata INT
SET @intdata = PATINDEX('%[^0-9]%', @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, '' )
SET @intdata = PATINDEX('%[^0-9]%', @var )
END
END

Select ISNULL(@var,0)

Find a float value from string :

DECLARE @var VARCHAR(100)='STA123.26dfg';
DECLARE @intdata INT
SET @intdata = PATINDEX('%[^0-9]%', @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, '' )
SET @intdata = PATINDEX('%[^0-9.]%', @var )
END
END

Select ISNULL(@var,0)

How to find the Database Status Report Through Alert

This one of the most important DBA Alert you should know the Status of the database ( online , Offline , suspected, Recovery , Read only, Emergency …etc)

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[DatabaseStatusReport]    Script Date: 02-Feb-16 1:37:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Rajendra Yadav
-- Create date: 02/02/2016 1:37:39 AM
-- Description: Check Database Status
-- =============================================
CREATE PROC [dbo].[DatabaseStatusReport]
@profile_name_P NVARCHAR(500) ='SQLProfile',@recipients_P NVARCHAR(500) ='raja09.mcp@gmail.com'
AS
BEGIN
SET NOCOUNT ON
DECLARE @p_subject NVARCHAR(500)
SET @p_subject = N'Offline Databases Report on DB Cluster ' + ( CAST((SELECT SERVERPROPERTY('ServerName')) AS NVARCHAR))

--Send the mail as table Formate
IF(SELECT COUNT(*) FROM sys.databases WHERE state_desc<>'Online')>0
BEGIN
DECLARE @table NVARCHAR(MAX) ;
SET @table =
N'<H2 style=" color: red; " >Offline Databases Report</H2>' +
N' <span style=” font-size: 16px;” >Urgnet this list by Databases not Accessible please take immediate action to fix it </span>' +
N'<table border="1">' +
N'<tr><th>Database Name</th><th>Database Status</th></tr>' +
CAST ( ( SELECT td=name, '',td=state_desc FROM sys.databases WHERE state_desc<>'Online'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name='SQLProfile', --Change to your Profile Name
@recipients='raja09.mcp@gmail.com', --Put the email address of those who want to receive the e-mail
@subject = @p_subject ,
@body = @table,
@body_format = 'HTML' ;
END
ELSE PRINT 'All Databases are Online'
END
GO

By this Stored Procedure any database will be under any status Except ONLINE status we will receive immediately  to receive email you should configure Database mail as i told you before plus you should create scheduled job to run every 10 or 15 Seconds(Depends) to Execute this Stored procedure.

Execute Stored procedure :

USE MSDB
GO
Exec DatabaseStatusReport 'SQLProfile','raja09.mcp@gmail.com'