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

26 October 2012 This 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...

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

Getting the mysql where in delimited string to workhttps://joellipman.com/articles/database/mysql/getting-the-mysql-where-in-delimited-string-to-work.html

02 January 2012 What? I have a mySQL database table of room assets that has a field containing the ID numbers of images relevant to this room. The Problem? When I select specifying the statement "WHERE IN (c.RoomImages)", this is interpreted as a string and when...

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

T-SQL functions to convert Strings to Tableshttps://joellipman.com/articles/database/t-sql/t-sql-functions-to-convert-strings-to-tables.html

20 August 2014 them on my site so I can refer to them more easily. How? Let's start with a function that just converts a string of words delimited by spaces to a table: CREATE FUNCTION dbo.[ufn_StringToTable] ( @StringInput VARCHAR(MAX) ) RETURNS @OutputTable TABLE (...

• 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 then lowercase subsequent alphas. -- NON-ALPHA EXCEPTIONS ADDED TO REGEXP (eg. apostrophe) WHILE (@i 0 BEGIN -- get the delimited word SET @CaseLen = CHARINDEX(',', @CaseExceptions, @i+1) - @i SET @CaseValue = SUBSTRING(@CaseExceptions, @i, @CaseLen) --...

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

Google Drive API v3 - OAuth2 using Service Account in PHP/JWThttps://joellipman.com/articles/google/google-drive-oauth-using-service-account-in-php.html

02 February 2019 you have not yet had the service account authorized by a G-Suite Administrator (see 2b). Scope is a string of scope urls delimited by a space (in this example only one). ISS is the client email taken from the JSON key downloaded from Google. // Build...

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

MS Excel - Open CSV with Long Numbershttps://joellipman.com/articles/microsoft/excel/ms-excel-open-csv-with-long-numbers.html

23 July 2019 the data source as a text file means that Excel will prompt you what to do with each column: 1. Specify that the data is delimited 2. Specify the data is delimited by commas 3. Specify which columns should be imported as text (the long numbers and phone...

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

Stored Procedure to List Distinct Values and Countshttps://joellipman.com/articles/database/t-sql/stored-procedure-to-list-distinct-values-and-counts.html

26 October 2012 Declare some variables to use DECLARE @ColToProcess varchar(max), @SqlToExecute varchar(max), @myXml xml; -- Parse comma delimited string into a table SELECT @myXml = CONVERT(xml,'' + REPLACE(@p_SearchColumns,',','') + ''); DECLARE Cursor1 CURSOR FOR...

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

Google Authentication - OAuth 2.0 using PHP/cURLhttps://joellipman.com/articles/google/google-authentication-oauth-2-0-using-php-curl.html

31 October 2018 process and if you store the credentials in a file, then the JWT is also in the file. A JWT is a three part value delimited by a period/dot. Base64 decode the second part and this will reveal the user's name and email (along with other profile settings)...

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

Zoho Deluge: Push Multi-Select Picklist containing Commas from CRM to Creatorhttps://joellipman.com/articles/crm/zoho/zoho-deluge-push-multi-select-picklist-containing-commas-from-crm-to-creator.html

20 April 2020 2"," 3"]} How? So the way to push this value over to Creator is by converting the list to a string... notably comma delimited: // Reading CRM: l_MyMultiPicklist = List:String(); l_MyMultiPicklist.add("Option1"); l_MyMultiPicklist.add("Options 2, 3"); //...

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

Zoho Deluge: Handle Commas between Quotes in a CSV (and New Lines)https://joellipman.com/articles/crm/zoho/zoho-deluge-handle-commas-between-quotes-in-a-csv.html

26 August 2021 // yields: 00011,Joel Lipman,"Flat 8|mySpecialComma| House Corner",Brummieland // split into a list (string delimited by commas) l_StringParts = v_FormattedString.toList(); // show me column 3 with myspecialcomma replaced back to a comma info...

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

Zoho Creator / Shopify: Get all Active Productshttps://joellipman.com/articles/crm/zoho/zoho-creator-shopify-get-all-active-products.html

12 October 2021 l_ProductIDs.size(); } Yields something like: [ 1234567890123, 2345678901234 ] 2 Well that produces a lot of ID numbers delimited by a comma. However, to match my use-case, I want a list to which I can give it an SKU, and it returns the Shopify Product...

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