Regular Expressions in SQLhttps://joellipman.com/articles/database/regular-expressions-in-sql.html

09 January 2014 for abnormal data, note the circumflex to exclude the clean alphanumeric rows: SELECT * FROM STUDENTS WHERE REGEXP_LIKE(S_SURNAME, '[^a-zA-Z0-9]') -- returns all rows where the student surname contains non-alphanumeric characters -- eg. O'Brien will be...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: *

PHP: First name and Initial of Surnamehttps://joellipman.com/articles/web-development/php/php-first-name-and-initial-of-surname.html

27 March 2014 What? A note for myself on some code to convert a string of two names into a string made up of the first name and then using the initial of the second name. -- What I have John Smith Fred.Bloggs -- What I want John S. Fred B. How? So different ways, the...

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: en-GB

SSRS: Performance Improvements: SELECT TOPhttps://joellipman.com/articles/microsoft/ssrs/ssrs-performance-improvements-select-top.html

07 March 2014 server says it did this in 0.4 seconds. So where's the problem? How? Left to its own devices, a search for "SMITH" as a surname would make the report spin for about 10 minutes without the possibility to cancel (link not working!?) and eventually would...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

T-SQL: Parse an XML valuehttps://joellipman.com/articles/database/t-sql/t-sql-parse-an-xml-value.html

27 March 2014 FROM Event_XML.nodes('/*/*') AS r(c) ) AS RootChild FROM [dbo].[XML_EVENTS] -- to get name of child node containing string "SURNAME" SELECT ( SELECT c.value('local-name(.)', 'nvarchar(50)') FROM...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Convert a delimited string to tablehttps://joellipman.com/articles/database/convert-a-delimited-string-to-table.html

26 October 2012 is a quick note to show you how to convert a given comma delimited string into a database table: Given: "Title,Forenames,Surname" Return: ID Value ------ ---------------- 1 Title 2 Forenames 3 Surname Note the below example omits the ID column and just...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: *

Returning Oracle Stored Procedure Resultset in SSRShttps://joellipman.com/articles/microsoft/ssrs/returning-oracle-stored-procedure-resultset-in-ssrs.html

19 January 2012 I want to display in SSRS OPEN l_CURSOR FOR SELECT INITCAP(b.p_title) || ' ' || INITCAP(b.p_forenames) || ' ' || UPPER(b.p_surname) || ' (' || INITCAP(To_char(b.p_dob, 'DD-MON-YYYY')) || ')' "p_STUDENT_DETAILS", NVL(a.student_id, b.person_id)...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

No rows returned in Oracle causes SP to failhttps://joellipman.com/articles/database/pl-sql/no-rows-returned-in-oracle-causes-sp-to-fail.html

24 January 2012 = trim(:p_myinparameter); SELECT NVL(student_details.studentFirstname, 'John') "FORENAMES", NVL(student_details.studentSurname, 'Smith') "SURNAME" FROM student_details WHERE student_details.studentID = v_studentreference; This errors when the student...

• Type: Article
• Author: Joel Lipman
• Category: Oracle PL/SQL
• Language: en-GB

Inline Labels in Form Fields using Javascripthttps://joellipman.com/articles/web-development/js/inline-labels-in-form-fields-using-javascript.html

21 August 2012 In this specific case, we are giving it the label value to indicate to the user to enter their Name (as in forename or surname). onFocus() - What to do when the user moves their cursor into the field or clicks on it. In this specific case, if the value...

• Type: Article
• Author: Joel Lipman
• Category: JavaScript
• Language: en-GB

T-SQL concatenate an incremental row numberhttps://joellipman.com/articles/database/t-sql/t-sql-concatenate-an-incremental-row-number.html

24 May 2013 Assume the following two tables exist: TABLE: Employee -------------------------------------------------- ID FirstName Surname 1 Joel Lipman 2 Another Joe TABLE: Qualifications -------------------------------------------------- ID Employee Subject Grade...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Convert to Proper Case in T-SQLhttps://joellipman.com/articles/database/t-sql/convert-to-proper-case-in-t-sql.html

04 June 2014 (JIMMY) C/O Dad's (Jimmy) SampleText SampleText lowercase.text lowercase.text Examples of usage: SELECT ufn_ProperCase([Surname]) FROM employees Additional: Note how the above doesn't convert postal codes, specifically British postcodes, which isn't...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Zoho Deluge - Generate 5 Letter Booking Retrieval Codehttps://joellipman.com/articles/crm/zoho/zoho-deluge-generate-5-letter-booking-retrieval-code.html

09 May 2019 Retrieval Codes as it is easy to determine what code if you have the date/time of the booking as well as the customer's surname (method #1 would not be guessed by date/time): string generateRetrievalCode() { // init v_Output = ""; l_Output =...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Trim in T-SQL and SSIShttps://joellipman.com/articles/microsoft/ssis/trim-in-t-sql-and-ssis.html

14 May 2013 in T-SQL where the trim is unecessary and a similar equation can be written: Table: DMExtractEmployeeTable ID ForeName SurName --------------------------------- 1 Joel Lipman 2 JOE THE GREATEST DECLARE @EmployeeStr = 'JOE ' + CHAR(9) + CHAR(10) +...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Integration Services
• Language: en-GB