DECLARE @Table Table (Id int Not NULL identity(1,1), Wert nvarchar(10)) Insert into @Table Values('A'),('B'),('C') Select * from @Table Select STRING_AGG(Wert,', ') WITHIN GROUP ( ORDER BY Wert ASC) as Liste from @Table
Gedanken zu SQL, Reports, ETL und CRM
DECLARE @Table Table (Id int Not NULL identity(1,1), Wert nvarchar(10)) Insert into @Table Values('A'),('B'),('C') Select * from @Table Select STRING_AGG(Wert,', ') WITHIN GROUP ( ORDER BY Wert ASC) as Liste from @Table
Seit SQL Server 2016 gibt es einige neue Funktionen. Ein Beispiel mit dem Code wesentlich kürzer ist die table based function „string_split()“.
Diese Funktion wird als Tabelle aufgerufen und hat nur die Spalte „value“:
/*Test Tabelle und Daten bereitstellen:*/ DECLARE @Test TABLE (id int identity(1,1) NOT NULL, wert nvarchar(MAX) NULL) INSERT INTO @test VALUES ('a,b,c,d,e,f'), ('1,2,3,4,5,6,7,8,9'), ('aa,bb,cc,dd,ee,ff,gg') --Select * from @Test /*Daten aus einem Feld als Tabelle bereitstellen:*/ Select value from @Test outer apply string_split(wert,',') where id = 3
Beispiel Code um aus mehreren Sätze eine „Kommaseparierte Liste“ zu haben:
IF EXISTS (select name from sysobjects where name = 'fod_implode' and Type = 'FN') Drop function fod_implode CREATE FUNCTION sysdba.fod_implode ( @id AS char(12)) RETURNS varchar(4000) BEGIN DECLARE @implode varchar(3000) Set @implode = '' DECLARE Name_Cursor CURSOR FOR SELECT lastname FROM sysdba.Contact WHERE accountid = @id DECLARE @Value varchar(255) OPEN Name_Cursor FETCH NEXT FROM Name_Cursor into @Value WHILE (@@FETCH_STATUS = 0) BEGIN if @implode = '' Set @implode = @Value else Set @implode = @implode + ', ' + @Value FETCH NEXT FROM Name_Cursor into @Value END CLOSE Name_Cursor DEALLOCATE Name_Cursor --Rueckgabewert: RETURN @implode END
IF EXISTS (select name, * from sysobjects where name = 'fod_YMD2Datetime' and Type = 'FN') Drop function fod_YMD2Datetime GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION fod_YMD2Datetime(@Year int, @Month int, @Day int) RETURNS datetime AS -- ============================================= -- Author: FOd -- Create date: <2009-01-30> -- Description: Aus Jahr, Monat und Tag ein Datetime liefern -- ============================================= BEGIN DECLARE @d datetime; SET @d = dateadd(year,(@Year - 1753),'1/1/1753'); SET @d = dateadd(month,@Month - 1,@d); SET @d = dateadd(day, @Day - 1, @d); RETURN @d END go --Select dbo.gc_YMD2Datetime(2009, 1, 30)
Quelle stammt IMHO von NovickSoftware.com
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF EXISTS (select name, * from sysobjects where name = 'fod_IsEmailValid' and Type = 'FN') Drop function fod_IsEmailValid GO CREATE FUNCTION [dbo].[fod_IsEmailValid] ( @EmailAddr varchar(255)) RETURNS BIT /* * Check a Emailaddress * Checks the text string to be sure it's a valid e-mail address. * Returns 1 when it is, otherwise 0. * 1 if @EmailAddr is a valid email address * Example: SELECT CASE WHEN 1=dbo.gc_IsEmailValid('anovick@NovickSoftware.com') THEN 'Is an e-mail address' ELSE 'Not an e-mail address' END * * Test: print case when 1=dbo.udf_txt_isEmail('anovick@novicksoftware.com') then 'Passes' else 'Fails' end + ' test for good addr' print case when 0=dbo.udf_txt_isEmail('@novicksoftware.com') then 'Passes' else 'Fails' end + ' test for no user' print case when 0=dbo.udf_txt_isEmail('anovick@n.com') then 'Passes' else 'Fails' end + ' test for 1 char domain' print case when 1=dbo.udf_txt_isEmail('anovick@no.com') then 'Passes' else 'Fails' end + ' test for 2 char domain' print case when 0=dbo.udf_txt_isEmail('anovick@.com') then 'Passes' else 'Fails' end + ' test for no domain' print case when 0=dbo.udf_txt_isEmail('anov ick@novicksoftware.com') then 'Passes' else 'Fails' end + ' test for space in name' print case when 0=dbo.udf_txt_isEmail('ano#vick@novicksoftware.com') then 'Passes' else 'Fails' end + ' test for # in user' print case when 0=dbo.udf_txt_isEmail('anovick@novick*software.com') then 'Passes' else 'Fails' end + ' test for * asterisk in domain' ****************************************************************/ AS BEGIN DECLARE @AlphabetPlus VARCHAR(255) , @Max INT -- Length of the address , @Pos INT -- Position in @EmailAddr , @OK BIT -- Is @EmailAddr OK -- Check basic conditions IF @EmailAddr IS NULL OR NOT @EmailAddr LIKE '_%@__%.__%' OR CHARINDEX(' ',LTRIM(RTRIM(@EmailAddr))) > 0 RETURN(0) SELECT @AlphabetPlus = 'abcdefghijklmnopqrstuvwxyz01234567890_-.@' , @Max = LEN(@EmailAddr) , @Pos = 0 , @OK = 1 WHILE @Pos < @Max AND @OK = 1 BEGIN SET @Pos = @Pos + 1 IF NOT @AlphabetPlus LIKE '%' + SUBSTRING(@EmailAddr, @Pos, 1) + '%' SET @OK = 0 END -- WHILE RETURN @OK END go --????? --GRANT EXEC on dbo.fod_IsEmailValid to PUBLIC go