SELECT table.column,
REPLACE(CAST(table.column as nvarchar(max)),'replace me', 'new text')
FROM bestellung b
WHERE table.column LIKE '%replace me%'
UPDATE table
SET table.column = REPLACE(CAST(table.column as nvarchar(max)),'replace me', 'new text')
WHERE table.column LIKE '%replace me%'
Veröffentlicht unter MSSQL.
declare @dt datetime, @dtEnd datetime;
set @dt = getdate(); /* Datum von */
set @dtEnd = dateadd(day, 100, @dt); /* Datum bis */

select convert(date, dateadd(day, number, @dt))
from
    (select distinct number from master.dbo.spt_values
     where name is null
    ) n
where dateadd(day, number, @dt) < @dtEnd

Veröffentlicht unter MSSQL.

Hier eine kleine Abfrage, um Doppelte Einträge in einer Tabelle anzuzeigen:

table1: Tabellenname

field_name: Feld welches zum vergleichen genutzt werden soll

Veröffentlicht unter MSSQL.
/* EXCEPT_COLUMNS FUNCTION */
IF EXISTS(SELECT * FROM sys.objects WHERE TYPE = 'P' AND name = 'EXCEPT_COLUMNS')
BEGIN
     DROP PROCEDURE EXCEPT_COLUMNS
END

IF NOT EXISTS(SELECT * FROM sys.objects WHERE TYPE = 'P' AND name = 'EXCEPT_COLUMNS')
BEGIN
EXEC dbo.sp_executesql @statement = N'
        CREATE PROCEDURE EXCEPT_COLUMNS
        (
            @TABLE_NAME VARCHAR(50),
            @COLUMNSLISTTOREMOVE VARCHAR(500),
            @Filter varchar(5000),
            @OrderBY varchar(5000)
        )
        AS
        BEGIN
            DECLARE @TEMP VARCHAR(5000)
            DECLARE @FINAL_COLUMNLIST VARCHAR(5000)
            DECLARE @QUERY VARCHAR(5000)

            SET @COLUMNSLISTTOREMOVE = REPLACE(@COLUMNSLISTTOREMOVE,'
','','''''','''''')
            SET @COLUMNSLISTTOREMOVE = '
'''''''+ @COLUMNSLISTTOREMOVE + ''''''''
            SET @FINAL_COLUMNLIST = '
'''

            SET @QUERY = '
'DECLARE C1 CURSOR FOR SELECT COLUMN_NAME FROM TEMPDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '''''' + @TABLE_NAME + ''%'''' AND COLUMN_NAME NOT IN (''+@COLUMNSLISTTOREMOVE+'')''  

            EXECUTE(@QUERY);
            OPEN C1
                FETCH NEXT FROM C1 INTO @TEMP
                WHILE @@FETCH_STATUS =0
                BEGIN
                    IF @FINAL_COLUMNLIST = '
'''
                    BEGIN
                        SET @FINAL_COLUMNLIST =QUOTENAME(@TEMP)
                    END
                    ELSE
                    BEGIN
                        SET @FINAL_COLUMNLIST = @FINAL_COLUMNLIST + '
','' + QUOTENAME(@TEMP)
                    END;
                    FETCH NEXT FROM C1 INTO @TEMP
                END
            CLOSE C1
            DEALLOCATE C1

            --SET @CList = @FINAL_COLUMNLIST;          

            SET @QUERY = '
'SELECT '' + @FINAL_COLUMNLIST + '' FROM '' + @TABLE_NAME;
            IF @Filter &lt;&gt; '
'''
            BEGIN
                SET @QUERY = @QUERY + '
' WHERE '' + @Filter
            END;
            IF @OrderBy &lt;&gt; '
'''
            BEGIN
                SET @QUERY = @QUERY + '
' '' + @OrderBy
            END;
            EXECUTE(@QUERY)
        END'
;
END;
/* EXCEPT_COLUMNS FUNCTION */
Veröffentlicht unter MSSQL.
/* SPLIT FUNCTION */
IF EXISTS (SELECT * FROM information_schema.routines WHERE routine_name = N'split' AND routine_type = 'function')
BEGIN
    DROP FUNCTION [dbo].[split]
END

IF NOT EXISTS (SELECT * FROM information_schema.routines WHERE routine_name = N'split' AND routine_type = 'function')
BEGIN
    EXEC dbo.sp_executesql @statement = N'
        CREATE FUNCTION dbo.split(@String VARCHAR(8000), @Delimiter VARCHAR(12), @returnItem INT)
        RETURNS VARCHAR(8000)
        AS
        BEGIN
            DECLARE @id INT;
            DECLARE @idx INT;
            DECLARE @slice VARCHAR(8000);
            DECLARE @returnslice VARCHAR(8000);

            SET @id = 0;
            SET @idx = 1;
            SET @returnslice = null;

            WHILE @idx!= 0
            BEGIN
                SET @id = @id + '
'1'';
                SET @idx = charindex(@Delimiter,@String);
                IF @idx!=0
                    SET @slice = left(@String,@idx - 1);
                ELSE
                    SET @slice = @String;
                IF @id = @returnItem SET @returnslice = @slice
                    SET @String = SUBSTRING(right(@String,len(@String) - @idx),len(@Delimiter), len(@String))
                IF len(@String) = 0 break
            END
            RETURN @returnslice
        END'
;
END;
/* SPLIT FUNCTION */
Veröffentlicht unter MSSQL.
/* TRIM FUNCTION */
IF EXISTS (SELECT * FROM information_schema.routines WHERE routine_name = N'trim' AND routine_type = 'function')
BEGIN
    DROP FUNCTION [dbo].TRIM
END

IF NOT EXISTS (SELECT * FROM information_schema.routines WHERE routine_name = N'trim' AND routine_type = 'function')
BEGIN
    EXEC dbo.sp_executesql @statement = N'
    CREATE FUNCTION [dbo].[trim]
    (
      @str nvarchar(MAX)
    )
    RETURNS nvarchar(MAX)
    AS
    /*
      http://sqltrim.codeplex.com

      Changes:
      2010-06-01    Heber Dijks
            - Replaced SPACE()-command with REPLICATE(), otherwise spaces of more than 8000 characters were trunc-ated

      2010-05-19    Heber Dijks
            - Changed input/output datatype to nvarchar
            - Reduced number of variables used
            - Changed datatype of length-variables to bigint, for MAX-length inputs
            - Optimized code

      2010-05-14    Heber Dijks
            Creation
    */
    BEGIN
      DECLARE @spaces bigint;

      -- Trim leading and trailing spaces
      SET @str = NULLIF(LTRIM(RTRIM(@str)),'
''');

      -- Do check existence of triple spaces (double spaces will be wiped at the end anyway)
      IF @str LIKE '
'%   %''
      BEGIN
        -- Calculate initial number of subsequent spaces to replace
        SET @spaces = FLOOR(SQRT(LEN(@STR)))
 -- minus two because first and last character are definitely not spaces

        WHILE 1=1
        BEGIN
          -- Replace spaces
          SET @str = REPLACE(@str,REPLICATE(CONVERT(nvarchar(max),'
' ''),@spaces),'' '');
 -- If not explicitly converted to xvarchar(max), spaces are trunc-ated to no more than 8000

          IF @spaces&lt;=2
            BREAK;

          -- Calculate new number of subsequent spaces to replace
          SET @spaces = SQRT((@spaces*2)-1);
        END;

        -- Do a replace of two spaces as a last step
        SET @str = REPLACE(@str,'
'  '','' '');
      END;

      -- Do a replace of two spaces only if double space exists
      IF @str LIKE '
'%  %''
        SET @str = REPLACE(@str,'
'  '','' '');

      RETURN @str;
    END'
;
END;
/* TRIM FUNCTION */
Veröffentlicht unter MSSQL.
/* fnFormatDate FUNCTION */
IF EXISTS (SELECT * FROM information_schema.routines WHERE routine_name = N'fnFormatDate' AND routine_type = 'function')
BEGIN
    DROP FUNCTION [dbo].fnFormatDate
END

IF NOT EXISTS (SELECT * FROM information_schema.routines WHERE routine_name = N'fnFormatDate' AND routine_type = 'function')
BEGIN
  EXEC dbo.sp_executesql @statement = N'
    CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
    RETURNS VARCHAR(32)
    AS
    BEGIN
        DECLARE @StringDate VARCHAR(32)
        SET @StringDate = @FormatMask
        IF (CHARINDEX ('
'YYYY'',@StringDate) &gt; 0)
           SET @StringDate = REPLACE(@StringDate, '
'YYYY'',
                             DATENAME(YY, @Datetime))
        IF (CHARINDEX ('
'YY'',@StringDate) &gt; 0)
           SET @StringDate = REPLACE(@StringDate, '
'YY'',
                             RIGHT(DATENAME(YY, @Datetime),2))
        IF (CHARINDEX ('
'Month'',@StringDate) &gt; 0)
           SET @StringDate = REPLACE(@StringDate, '
'Month'',
                             DATENAME(MM, @Datetime))
        IF (CHARINDEX ('
'MON'',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)&gt;0)
           SET @StringDate = REPLACE(@StringDate, '
'MON'',
                             LEFT(UPPER(DATENAME(MM, @Datetime)),3))
        IF (CHARINDEX ('
'Mon'',@StringDate) &gt; 0)
           SET @StringDate = REPLACE(@StringDate, '
'Mon'',
                                         LEFT(DATENAME(MM, @Datetime),3))
        IF (CHARINDEX ('
'MM'',@StringDate) &gt; 0)
           SET @StringDate = REPLACE(@StringDate, '
'MM'',
                      RIGHT('
'0''+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
        IF (CHARINDEX ('
'M'',@StringDate) &gt; 0)
           SET @StringDate = REPLACE(@StringDate, '
'M'',
                             CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
        IF (CHARINDEX ('
'DD'',@StringDate) &gt; 0)
           SET @StringDate = REPLACE(@StringDate, '
'DD'',
                             RIGHT('
'0''+DATENAME(DD, @Datetime),2))
        IF (CHARINDEX ('
'D'',@StringDate) &gt; 0)
           SET @StringDate = REPLACE(@StringDate, '
'D'',
                                         DATENAME(DD, @Datetime))
    RETURN @StringDate
    END'
;
END;
/* fnFormatDate FUNCTION */
Veröffentlicht unter MSSQL.
/* ISOweek FUNCTION */
IF EXISTS (SELECT * FROM information_schema.routines WHERE routine_name = N'ISOweek' AND routine_type = 'function')
BEGIN
    DROP FUNCTION [dbo].[ISOweek]
END

IF NOT EXISTS (SELECT * FROM information_schema.routines WHERE routine_name = N'ISOweek' AND routine_type = 'function')
BEGIN
    EXEC dbo.sp_executesql @statement = N'
        CREATE FUNCTION ISOweek  (@DATE DATETIME)
    RETURNS INT
    AS
    BEGIN
       DECLARE @ISOweek INT
       SET @ISOweek = DATEPART(wk,@DATE)
                      +1
                      -DATEPART(wk,CAST(DATEPART(yy,@DATE) AS CHAR(4))+'
'0104'')
       IF (@ISOweek=0)
          SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy,@DATE) - 1
                         AS CHAR(4))+'
'12''+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
       IF ((DATEPART(mm,@DATE)=12) AND
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))&gt;= 28))
          SET @ISOweek=1
       RETURN(@ISOweek)
    END'
;
END;
/* ISOweek FUNCTION */
Veröffentlicht unter MSSQL.