CAT | MS SQL
Problem:
How to match whole word in SQL.
We very frequently face the requirement to match an entire word, while developing the website/application.
Solution:
This problem can be very easily handled by Microsoft SQL query.
For example when you search for “ram”, if you use “%ram%” it will also match “programme”.
To resolve this, we can use regular expression like syntax in SQL.
The below query will only return the title having “ram” and not “programme”.
select title from article where ‘ ‘+title like ‘%[ ]ram[ ]%’
Additionally we also can provide extra characters to match after word like
select title from article where ‘ ‘+title like ‘%[ -_]ram[ .-_]%’
Enjoy working with SQL.
Problem:
We face the problem of how to find out the first day and last day of a week or a month, while developing a website/application.
Solution:
This can simply be calculated with the help of SQL queries. We can have a query executed, which ultimately calls a SQL function to do the needful. Please find below the query and the function for the same.
Query:
This query calculates the first and the last day of a week, by keeping the current date as the point of reference. The current date can be retrieved directly from SQL, by using its default function getdate().
declare @Sundayofweek datetime,@saturdayofweek datetime
declare @Lastdayoflastmonth datetime,@Firstdayofnextmonth datetime
set @Sundayofweek=(SELECT Sunday FROM dbo.DisplayCurrentWeekDateDays(dbo.Getdate()))
set @saturdayofweek=(SELECT saturday FROM dbo.DisplayCurrentWeekDateDays(dbo.Getdate()))
set @Lastdayoflastmonth=(DATEADD(dd,-(DAY(dbo.Getdate())),dbo.Getdate()))
set @Firstdayofnextmonth=(DATEADD(dd,-(DAY(DATEADD(mm,1,dbo.Getdate()))-1),DATEADD(mm,1,dbo.Getdate())))
The above query can return the following results:
- Sunday as the first day of the current week
- Saturday as the last day of the current week
- The first day of the current month
- The last day of the current month
Function:
This function returns the required day of the week, based on the current date passed as a parameter from the above query.
ALTER FUNCTION [dbo].[DisplayCurrentWeekDateDays]
(@today SMALLDATETIME)
RETURNS @WeekDateDay TABLE
(
Sunday SMALLDATETIME,
Monday SMALLDATETIME,
Tuesday SMALLDATETIME,
Wednesday SMALLDATETIME,
Thursday SMALLDATETIME,
Friday SMALLDATETIME,
Saturday SMALLDATETIME
)
AS
BEGIN
DECLARE @day INT
SET @today = CAST(CONVERT(VARCHAR(10), @today, 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)
INSERT INTO @WeekDateDay (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)
SELECT DATEADD(dd, 1 – @day, @today) Sunday,
DATEADD(dd, 2 – @day, @today) Monday,
DATEADD(dd, 3 – @day, @today) Tuesday,
DATEADD(dd, 4 – @day, @today) Wednesday,
DATEADD(dd, 5 – @day, @today) Thursday,
DATEADD(dd, 6 – @day, @today) Friday,
DATEADD(dd, 7 – @day, @today) Saturday
RETURN
END
This worked for me.