MSSQL « Marcel Beck

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%'

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

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

SELECT tabl1, 
 COUNT(field_name) AS NumOccurrences
FROM tabl1
GROUP BY field_name
HAVING ( COUNT(field_name) > 1 )

SELECT tabl1,

COUNT(field_name) AS NumOccurrences

FROM tabl1

GROUP BY field_name

HAVING ( COUNT(field_name) > 1 )

table1: Tabellenname

field_name: Feld welches zum vergleichen genutzt werden soll

/* 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 <> ''''
BEGIN
SET @QUERY = @QUERY + '' WHERE '' + @Filter
END;
IF @OrderBy <> ''''
BEGIN
SET @QUERY = @QUERY + '' '' + @OrderBy
END;
EXECUTE(@QUERY)
END';
END;
/* EXCEPT_COLUMNS FUNCTION */

/* 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 */

/* 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<=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 */

/* 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) > 0)
SET @StringDate = REPLACE(@StringDate, ''YYYY'',
DATENAME(YY, @Datetime))
IF (CHARINDEX (''YY'',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ''YY'',
RIGHT(DATENAME(YY, @Datetime),2))
IF (CHARINDEX (''Month'',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ''Month'',
DATENAME(MM, @Datetime))
IF (CHARINDEX (''MON'',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
SET @StringDate = REPLACE(@StringDate, ''MON'',
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
IF (CHARINDEX (''Mon'',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ''Mon'',
LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX (''MM'',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ''MM'',
RIGHT(''0''+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX (''M'',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ''M'',
CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
IF (CHARINDEX (''DD'',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ''DD'',
RIGHT(''0''+DATENAME(DD, @Datetime),2))
IF (CHARINDEX (''D'',@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ''D'',
DATENAME(DD, @Datetime))
RETURN @StringDate
END';
END;
/* fnFormatDate FUNCTION */

/* 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))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END';
END;
/* ISOweek FUNCTION */

Marcel Beck

Ich mag Züge…

Archiv der Kategorie: MSSQL

MSSQL – Replace Text in Text

MSSQL – Liste aus Datumsangaben von Datum bis Datum erstellen

MSSQL – Doppelte Einträge

MSSQL – Except Columns Funktion

MSSQL – Split Funktion

MSSQL – Trim Funktion

MSSQL – format Date Funktion

MSSQL – ISO Week Funktion