Remove non alphabetic character from string in SQL Server

Query to remove non alphabetic character from string in sql server:


DECLARE @str varchar(100) = 'Paresh@#$Patel#$!@#888'

SELECT CAST(
  (SELECT CASE
           WHEN SUBSTRING(@str, n, 1) LIKE '[A-Z]'
           THEN SUBSTRING(@str, n, 1)
           ELSE ''
          END
  FROM (SELECT number
        FROM master..spt_values
        WHERE type = 'P'
          AND number BETWEEN 1 AND 50) AS Number(n)
  WHERE n <= LEN(@str)
  FOR XML PATH('')) AS NVARCHAR(100))

Run this query you will get the output like :

PareshPatel

Find sequential discharge date from multiple discharge date

Create one table variable and insert the data as need :


DECLARE @table table(patID INT,AdmDate Datetime,DisDate Datetime)

INSERT INTO @table
values(1,'2012-01-10','2012-01-15'),
(1,'2012-01-16','2012-01-20'),
(1,'2012-01-21','2012-01-25'),
(2,'2012-01-10','2012-01-15'),
(2,'2012-01-17','2012-01-20')

After inserting data here is the query to find the sequential discharge date.


;WITH cte (PatientId, AdmDt, Disdt, AdmDt2, Rnk) AS
(
SELECT t1.PatientID, t1.AdmissionDate, t1.DischargeDate, t2.AdmissionDate, ROW_NUMBER() OVER(PARTITION BY t1.PatientId ORDER BY t1.PatientId, t1.AdmissionDate, t1.DischargeDate) AS Rnk
FROM @table t1
LEFT JOIN @table t2 ON (t1.PatientID = t2.PatientID AND DATEADD(DAY, 1, t1.DischargeDate) = t2.AdmissionDate)
),
cte1 (PatientId, AdmDt, DisDt, NextRnk) AS
(
SELECT D1.PatientId, D1.AdmDt, D1.Disdt,
CASE WHEN D1.AdmDt2 IS NULL THEN D1.Rnk ELSE  MIN(D2.Rnk) End AS NextRnk
FROM cte D1
LEFT JOIN cte D2 ON (D1.PatientId = D2.PatientId AND D2.Rnk > D1.Rnk AND D2.AdmDt2 IS NULL)
GROUP BY D1.PatientId, D1.AdmDt, D1.Disdt,D1.AdmDt2, D1.Rnk
)
SELECT D1.PatientId, MIN(D1.AdmDt) AS AdminssionDate, MAX(D1.DisDt) AS DischargeDate
FROM cte1 D1
GROUP BY D1.PatientId, D1.NextRnk
ORDER BY D1.PatientId, D1.NextRnk

This will gives the output as :

Find physical path for Database backup file in SQL Server

This is the query to find the physical location for backed up database file.


SELECT        
physical_device_name,
    backup_start_date,
    backup_finish_date,
    backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bm ON bs.media_set_id = bm.media_set_id
WHERE database_name = 'Testdb'
ORDER BY backup_finish_date DESC

Output :

Get XML Response From Web URL

First set the following option in sql :
sp_configure 'show advanced option',1
reconfigure

Sp_configure 'Ole Automation Procedures',1
Reconfigure

Then after run the following script by passing your web URL to get the XML response.

DECLARE @Object as Int;
Declare @ResponseText as Varchar(8000);
Declare @Url as Varchar(MAX);
select @Url = 'http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml'

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Exec sp_OADestroy @Object

--load into Xml
Declare @XmlResponse as xml;


select @XmlResponse = CAST(@ResponseText as xml)
select @XmlResponse

Find exact Age using Birth date

Create this scalar function to get the exact Age.

ALTER FUNCTION GetAgeByBirthDate(
@BirthDate DATETIME,
@CurrentDate DATE
)
RETURNS VARCHAR(200)
BEGIN
DECLARE @Year INT,
@Month INT,
@Day INT,
@Age VARCHAR(200)



IF MONTH(@BirthDate) <> MONTH(@CurrentDate) BEGIN
SELECT @Year = DATEDIFF(yy,@BirthDate,@CurrentDate)-1
SELECT @Month = DATEDIFF(mm,DATEADD(yy,@Year,@BirthDate),@CurrentDate)

END ELSE BEGIN
SELECT @Year = DATEDIFF(yy,@BirthDate,@CurrentDate)
SELECT @Month = 0
END

IF DAY(@BirthDate) <= DAY(@CurrentDate) BEGIN
SELECT @Day = DATEDIFF(dd,DATEADD(mm,@Month,DATEADD(yy,@year,@BirthDate)),@CurrentDate)
END ELSE BEGIN
SELECT @Day = DATEDIFF(dd,DATEADD(mm,@Month-1,DATEADD(yy,@year,@BirthDate)),@CurrentDate)
END

SELECT @Age = 'Your age is ' + CAST(@Year AS VARCHAR(4)) + ' YEARS ' + CAST(@Month AS VARCHAR(4)) + ' MONTHS and ' + CAST(@Day AS VARCHAR(4)) + ' DAYS.'

RETURN @Age
END

After creating this function run this query :
SELECT dbo.GetAgeByBirthDate('1981-12-19',GETDATE())

First argument is the birth date and second is the current date.