SQL Server | Remove Special Characters

I needed to remove all the special characters from a field in a particular table in SQL Server to be used for searching purposes. After searching (binging )… I found the next SQL Server Function which did the trick.

This Function Removes any special character from the string value passed. All characters except 0-9, a-z and A-Z are all removed, whilst the remaining characters are returned back.

ALTER FUNCTION dbo.RemoveSpecialCharacters
(
@s VARCHAR(256)
)
RETURNS VARCHAR(256) WITH SCHEMABINDING

BEGIN
IF (@s is null)
RETURN null Continue reading »

Leading Caps in Crystal Reports

When designing a report in Crystal Reports, a common problem is the text to retrieve from the database. Sometimes the text is all in Capital Letters, or Small Letters, and a few times it’s ok.

Crystal Reports can only transform the text either in Small Caps or All Caps. To have the text written in Leading Caps you have to do the following… Continue reading »

Count Working Days

Sometimes people need to count the difference between two dates. In Sql Server 2000 and 2005 it’s not a problem. To count the difference between the days you van use the following statement:

DATEDIFF(hh, Date 1, Date 2, ) / 24

The difficulty comes when you want to count only the working days of the dates. Maybe not many developers have this problem… but i couldn’t find a solution. After searching i found this function…


CREATE FUNCTION dbo.GetWorkingDays
( Continue reading »