PlItems

ID: 208
PlCodelang: PL/pgSQL
PlGroup: Datenbank
PlItemTitle: Tabelleinhalt DB übergreifend kopieren
Code: -- siehe auch: http://stackoverflow.com/questions/6083132/postgresql-insert-into-select -- in DestinationDB do: INSERT INTO Table1 SELECT id, valuefield FROM dblink('dbname=SourceDB', 'SELECT id, valuefield FROM Table1') AS t(id integer, valuefield varchar(100));

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

ID: 134
PlCodelang: SSIS
PlGroup: Fallunterscheidung
PlItemTitle: Wenn-Dann Abfrage
Code: ' Using IF(IIF) logic in Derived Column (LEN(TRIM(Test)) > 0 ? SUBSTRING(Test,1,5) : NULL(DT_WSTR,5))

ID: 260
PlCodelang: SQLite
PlGroup: Datenbank
PlItemTitle: Temporäre DB-Tabelle
Code: CREATE TEMPORARY TABLE myTempTable1 ( id INT ,myText TEXT )

ID: 263
PlCodelang: SQLite
PlGroup: Datenbank
PlItemTitle: CTAS
Code: CREATE TABLE newDestinationTable AS SELECT * FROM oldSourceTable

ID: 264
PlCodelang: SQLite
PlGroup: Datenbank
PlItemTitle: Eine Menge B reduziert um Menge A
Code: -- Es soll die Menge an Datensätze ausgegeben reduziert um eine andere Menge (Rest) -- Die einzelnen Tabellen oder Subqueries müssen die gleichen Spalten im Statement haben SELECT FieldA ,FieldB ,FieldC FROM myTable1 EXCEPT SELECT FieldA ,FieldB ,FieldC FROM myTable2

ID: 291
PlCodelang: SQLite
PlGroup: Strings
PlItemTitle: Führende Nullen vor einer Zahl
Link Reference: https://stackoverflow.com/questions/4899832/sqlite-function-to-format-numbers-with-leading-zeroes
Code: WITH cte_example AS ( SELECT 5 AS int_example UNION ALL SELECT 10 AS int_example UNION ALL SELECT 50 AS int_example ) SELECT int_example, substr('0000000'||ROW_NUMBER() OVER(ORDER BY int_example)||'00', -8, 8) AS example_result FROM cte_example ;
Result Example:

int_example	example_result
5	00000100
10	00000200
50	00000300

ID: 141
PlCodelang: SQLite
PlGroup: Datenbank
PlItemTitle: Fremdschlüsselprüfung aktivieren
Code: -- http://www.sqlite.org/foreignkeys.html PRAGMA foreign_keys = ON; -- !! this is only valid for the current session. -- If you want to use this permanent, you have to use this statement every time you start a new session

ID: 168
PlCodelang: SQLite
PlGroup: Datum, Zeit
PlItemTitle: Erster und letzter Tag des Monats ermitteln
Code: select date('now','start of month'); --first day of current month select date('now','start of month', '+1 months','-1 day'); --last day of current month

ID: 169
PlCodelang: SQLite
PlGroup: Datum, Zeit
PlItemTitle: Aktuelles Datum/Uhrzeit
Code: select date('now'); select time('now'); select datetime('now');
Result Example:

2008-01-16 14:16:01 2008-01-16 14:16:01

ID: 171
PlCodelang: SQLite
PlGroup: Datum, Zeit
PlItemTitle: Gestriges Datum
Code: select date('now','-1 day');

ID: 172
PlCodelang: SQLite
PlGroup: Datum, Zeit
PlItemTitle: Zeitdifferenz
Link Technical Documentation: https://www.sqlite.org/lang_datefunc.html
Code: -- http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions SELECT (strftime('%s','now') - strftime('%s',date('now','-1 day'))) AS Seconds -- https://stackoverflow.com/questions/289680/difference-between-2-dates-in-sqlite SELECT CAST(JulianDay('now') - JulianDay(MAX(datetime_field_in_table)) * 24 * 60 AS Integer) AS diff_minutes FROM example_table

ID: 209
PlCodelang: SQLite
PlGroup: Datum, Zeit
PlItemTitle: Kalenderwoche eines Datums
Code: -- Standard Weeknumber function from SQLite: -- http://www.sqlite.org/lang_datefunc.html SELECT strftime('%W', '2014-08-25') -- The above calculation can lead to an wrong number if you expect an iso calendar week. -- Therefore better: -- http://stackoverflow.com/questions/15082584/sqlite-return-wrong-week-number-for-2013 SELECT (strftime('%j', date('2014-08-25', '-3 days', 'weekday 4')) - 1) / 7 + 1 AS ISOCalendarWeekNumber;

ID: 211
PlCodelang: SQLite
PlGroup: Datum, Zeit
PlItemTitle: Jahr aus Datum extrahieren
Code: SELECT strftime('%Y', '2014-08-25') AS Year;

ID: 212
PlCodelang: SQLite
PlGroup: Datum, Zeit
PlItemTitle: Tagesname
Code: -- There is no build in solution. Because of this you have to build a solution out of the weekday. -- Be careful, the first weekday (0) is a sunday SELECT ,CASE CAST(strftime('%w', myDateField) as integer) WHEN 0 then 'Sonntag' WHEN 1 then 'Montag' WHEN 2 then 'Dienstag' WHEN 3 then 'Mittwoch' WHEN 4 then 'Donnerstag' WHEN 5 then 'Freitag' WHEN 6 then 'Samstag' ELSE '????????????????????' END AS Tag_der_Woche_Deutsch ,CASE CAST(strftime('%w', myDateField) as integer) WHEN 0 then 'Sunday' WHEN 1 then 'Monday' WHEN 2 then 'Tuesday' WHEN 3 then 'Wednesday' WHEN 4 then 'Thursday' WHEN 5 then 'Friday' WHEN 6 then 'Saturday' ELSE '????????????????????' END AS Day_of_week_English

ID: 223
PlCodelang: SQLite
PlGroup: Strings
PlItemTitle: Strings miteinander verbinden
Code: SELECT field_a ,field_b ,field_a || field_b as together FROM table_1

ID: 142
PlCodelang: JavaScript
PlGroup: URL-Handling
PlItemTitle: Aktuelle URL bekommen
Code: // http://www.w3.org/TR/DOM-Level-2-HTML/html.html#ID-46183437 myURL = document.URL; // => http://myDNSNameOrIP:1234/path/testfile.html

ID: 143
PlCodelang: JavaScript
PlGroup: URL-Handling
PlItemTitle: Domain und Port einer URL
Code: domainAndPort = document.URL.split("/")[2]; // => 192.168.1.100:1234 // => myDNSname.com:6789

ID: 144
PlCodelang: JavaScript
PlGroup: Strings
PlItemTitle: String nach Delimenter zerlegen
Code: myTestString = "abc;def;ghj/1"; splittedString = myTestString.split(";"); elementTwo = splittedString[1]; // => def

ID: 145
PlCodelang: JavaScript
PlGroup: Strings
PlItemTitle: Anzahl an Zeichen in einem String ermitteln
Code: myString = "This is a string, containing some words. Try it, if you don't believe ;-)"; // Get the count of commas in the string: cntCommas = myString.split(",").length-1; // => 2