PlItems

ID: 180
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: NULL Wert eines Feldes übersteuern
Code: SELECT ISNULL(MyColumn1, 1.0) AS MyColumnWithoutNULL;

ID: 185
PlCodelang: T-SQL
PlGroup: Strings
PlItemTitle: Prüfen ob eine Zahl in einem String enthalten ist
Code: SELECT value ,CASE WHEN PATINDEX('%[0-9]%',value) > 0 THEN 1 ELSE 0 END AS CheckIfStringContainsANumber FROM ( SELECT 'String w/o a number' AS value UNION ALL SELECT 'This string contains 1 as a number' AS value ) subQuery1 -- value | CheckIfStringContainsANumber -- ------------------------------------------------------------------ -- String w/o a number | 0 -- This string contains 1 as a number | 1

ID: 207
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: Manuell einen AutoIncrement/Identity/Serial Wert in Tabelle einfügen
Code: SET IDENTITY_INSERT myTable ON INSERT INTO myTable(Identity_ID, Value) VALUES (4711, 'Back To The Future...') SET IDENTITY_INSERT myTable OFF

ID: 210
PlCodelang: T-SQL
PlGroup: Datum, Zeit
PlItemTitle: Kalenderwoche eines Datums
Code: select DATEPART(ISO_WEEK,CAST('2014-08-25' AS DATE))

ID: 213
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: DB-Funktion erstellen
Code: -- Table-value -- Variant 1 CREATE FUNCTION whichContinent (@country nvarchar(15)) RETURNS TABLE AS RETURN ( SELECT @country AS Country ) -- Variant 2 CREATE FUNCTION whichContinent (@country nvarchar(15)) RETURNS @returnTableResult TABLE ( id int ,currency VARCHAR(100) ) AS BEGIN IF @country='Germany' BEGIN INSERT INTO @returnTableResult VALUES (1,'EURO') END RETURN END

ID: 214
PlCodelang: T-SQL
PlGroup: Exceptions
PlItemTitle: Try Catch Except
Code: -- See also: http://msdn.microsoft.com/de-de/library/ms175976(v=sql.105).aspx -- !! -- TRY-CATCH cannot be used for all operations/situations in T-SQL. -- Be careful and refer to the link above for details -- !! BEGIN TRY SELECT 1/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH;

ID: 217
PlCodelang: T-SQL
PlGroup: Datenmengen
PlItemTitle: Anzahl Zeilen Resultset begrenzen
Code: SELECT TOP 100 * FROM tableA

ID: 220
PlCodelang: T-SQL
PlGroup: Strings
PlItemTitle: Position eines Teilstrings finden
Code: DECLARE @searchFor_Charindex Varchar(10) DECLARE @searchFor_Patindex Varchar(10) DECLARE @text Varchar(100) SET @searchFor_Charindex = 'simple' SET @searchFor_Patindex = '%simple%' -- Remember the %. Patindex can also be used for RegEx! SET @text = 'this is a simple text' SELECT CHARINDEX(@searchFor_Charindex, @text) AS Position_Charindex SELECT PATINDEX(@searchFor_Patindex, @text) AS Position_Patindex -- 11

ID: 226
PlCodelang: T-SQL
PlGroup: Systeminfo
PlItemTitle: Hostnamen ermitteln
Code: SELECT HOST_NAME() AS Hostname; -- prints myComputer1 or srvWindows1

ID: 241
PlCodelang: T-SQL
PlGroup: Strings
PlItemTitle: Text in Großschrift oder Kleinschrift
Code: SELECT 'my lovely Mr Singing Club' AS myString ,UPPER('my lovely Mr Singing Club') AS myStringUpper ,LOWER('my lovely Mr Singing Club') AS myStringLower -- myString: my lovely Mr Singing Club -- myStringUpper: MY LOVELY MR SINGING CLUB -- myStringLower: my lovely mr singing club

ID: 244
PlCodelang: T-SQL
PlGroup: Datenbank
PlItemTitle: SQL SELECT Statische Werte
Code: SELECT * FROM ( VALUES (1, 2), (3, 4) ) AS q (col1, col2) GO -- the same as: SELECT 1 AS col1, 2 AS col2 UNION ALL SELECT 3 AS col1, 4 AS col2 GO -- col1 | col2 -- =========== -- 1 | 2 -- ----------- -- 3 | 4

ID: 253
PlCodelang: T-SQL
PlGroup: Metadaten
PlItemTitle: Fremdschlüssel auflisten
Link Reference: https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server
Code: SELECT [FK_NAME] = obj.name ,[Schema_Name] = sch.name ,[Table] = tab1.name ,[Column] = col1.name ,[Referenced_Schema_Name] = sch2.name ,[Referenced_Table] = tab2.name ,[Referenced_Column] = col2.name FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id INNER JOIN sys.schemas sch2 ON tab2.schema_id = sch.schema_id
Result Example:

FK_NAME	Schema_Name	Table	Column	Referenced_Schema_Name	Referenced_Table	Referenced_Column
CountryFK	dbo	Adress	FK_Country	dbo	Countied	ID

ID: 69
PlCodelang: C#
PlGroup: GUI, .NET-WPF
PlItemTitle: Ressource aus separater Style-Ressource
Code: var tbo = new TextBlock { Style = (Style)Application.Current.FindResource("MyTextBlockStyle") };

ID: 75
PlCodelang: C#
PlGroup: Typenkonvertierung
PlItemTitle: String in Integer konvertieren
Code: int MyInt if (Int32.TryParse("125", out MyInt)) { // Parsing was successfull }

ID: 84
PlCodelang: C#
PlGroup: Variablen
PlItemTitle: Darstellung eines Unicode Zeichens in C#
Code: //Hier wird in die Variable "semikolon" das Semikolon eingetragen var semikolon = (char)59;

ID: 85
PlCodelang: C#
PlGroup: Array
PlItemTitle: Deklarieren eines Arrays in C#
Code: // Array direkt befüllen string[] stringArray = {"a", "b", "c"}; // Array mit fester Länge string[] stringArray = new string[5];

ID: 86
PlCodelang: C#
PlGroup: Variablen
PlItemTitle: Parsen von Datentypen
Code: var d = (double)integer; var myButton = (Button)object;

ID: 87
PlCodelang: C#
PlGroup: Strings
PlItemTitle: String Leerstellen entfernen
Code: string myString = " test "; Console.WriteLine(myString.Trim()); //Gibt: "test" aus.

ID: 88
PlCodelang: C#
PlGroup: Strings
PlItemTitle: String auf Wunschlänge zuschneiden
Code: string myString = "Benzinverbrauch"; Console.WriteLine(myString.Substring(2, 8)); //Ausgabe: "nzinverb"

ID: 89
PlCodelang: C#
PlGroup: Kontrollstrukturen
PlItemTitle: Switch / Case Anweisung
Code: int i = 1; switch (i) { case 1: Console.WriteLine("Fall 1"); break; case 2: Console.WriteLine("Fall 2"); break; default: Console.WriteLine("Standard Fall"); break; }