Monday, September 30, 2013

fnTitleCase

CREATE FUNCTION [dbo].[fnTitleCase]( @text AS varchar(8000) )
RETURNS varchar(8000)
AS
BEGIN
   DECLARE @Reset bit;
   DECLARE @Ret varchar(8000);
   DECLARE @i int;
   DECLARE @c char(1);

   SELECT @Reset = 1, @i=1, @Ret = '';

   WHILE (@i <= len(@Text))
    BEGIN
       SELECT @c= substring(@Text,@i,1),
                  @Ret = @Ret + case when @Reset=1 then UPPER(@c) 
                                                   else LOWER(@c) end,
                  @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
                  @i = @i +1
    END
   RETURN @Ret
END

Friday, September 27, 2013

To move a table from one filegroup to another while the system is "live" and "online", recreate its clustered index...  Be sure the target filegroup is already sized to handle the incoming data :)


ALTER DATABASE databaseName
MODIFY FILE
    (NAME = datafileLogicalName,
    SIZE = 2000MB);
GO



CREATE CLUSTERED INDEX [i_tableName_cu] ON [dbo].[tableName] 
(
[columName] ASC
)
WITH DROP_EXISTING
ON [filegroupName]
GO
To consolidate database files:

DBCC SHRINKFILE('logicalFileName', EMPTYFILE);
ALTER DATABASE databaseName REMOVE FILE logicalFileName;

Friday, September 20, 2013

fnGetVal


ALTER FUNCTION [dbo].[fnGetVal]( @s varchar(8000), @label varchar(1000) )
RETURNS varchar(1000)
AS
BEGIN
   DECLARE @value varchar(1000)
         , @value_start int
         , @value_end int
         
   SET @value_start = charindex(@label,@s)
   
   IF @value_start > 0
    BEGIN
      SET @value_start += len(@label) + 3
      SET @value_end = charindex(',',@s,@value_start)

      IF @value_end > @value_start
       BEGIN
         SET @value = substring(@s,@value_start,@value_end-@value_start)
       END
      ELSE
       BEGIN
         SET @value = substring(@s,@value_start,len(@s)-@value_start+1)
       END
    END

 RETURN ltrim(rtrim(@value))
END

Tuesday, September 17, 2013

good tools

a good tool will make something you already do, easier

never get a tool expecting that the tool will make you do something you are not already doing...

it is similar to exercise equipment...

if you are not already doing push-ups, sit-ups, and jogging, a gym membership or purchasing gym equipment will not make you start exercising...