PlItems

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

ID: 199
PlCodelang: JavaScript
PlGroup: Strings
PlItemTitle: String auf Wunschlänge zuschneiden
Code: myString = "abc"; mySubString = myString.substring(0,2); // ab

ID: 200
PlCodelang: JavaScript
PlGroup: Datum, Zeit
PlItemTitle: Aktuelles Datum/Uhrzeit
Code: date = new Date(); // Sat Jun 14 2014 13:15:27 GMT+0200 (CEST) dateStringISO = date.toISOString(); // "2014-06-14T11:15:27.097Z" // Attention: Time is different because of the timezone date.toISOString().substring(0,10); //"2014-06-14" dateStringISO.split("T")[1]; // 11:15:27.097Z

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: 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: 269
PlCodelang: PL/pgSQL
PlGroup: Datenbank
PlItemTitle: UPDATE SQL mit JOIN
Code: UPDATE TableToBeUpdated SET myField1 = 'Update this field' FROM ( SELECT * FROM JoinedTable WHERE cond1 = 1 ) J WHERE J.ID = TableToBeUpdated.ID ;

ID: 275
PlCodelang: PL/pgSQL
PlGroup: Datenbank
PlItemTitle: Stored Procedure anlegen/löschen
Code: CREATE OR REPLACE FUNCTION increment(i INT) RETURNS void AS $$ BEGIN INSERT INTO accounts (id,name, balance) VALUES (i, 'Test', 1200); END; $$ LANGUAGE plpgsql; DROP FUNCTION increment(i INT);
Result Example:

CREATE FUNCTION DROP FUNCTION