Split Name Field in 2

V5 Question
1

Is it possible to split the Name in the Name field into First Name, Last Name? If not I will add two extra Custom Fields.

If I do that are there any ramifications in having the Name field blank (or even deleting the Name field altogether)?

2

Where do you need to use the split values?
A quick script could seperate on first space or other pattern.

3

I want to use the first name for kitchen/coffee order tickets. We have a lot of regular customers so when they ‘tag’ in their name will show on the ticket.

4

At first create script.
image

function split(name) {
var str = name;
var res = str.split(" ");
return res[0]
}

Then paste code below to ticket template where you want.
{CALL:splitd.split(’{ENTITY NAME}’)}

If your customer name is “salim arda kose”. will write only “salim”

image

image

if you want to write “Arda” use this script

function split(name) {
var str = name;
var res = str.split(" ");
return res[1]
}

or for write “köse” use this

function split(name) {
var str = name;
var res = str.split(" ");
return res[2]
}

only change 0,1,2, …
.

5

Awesome, thanks for that, I’ll test it out tomorrow.
Cheers :+1:

6

[='{ENTITY NAME}'.replace(/ (.*)/g,"")]

3 Likes
7

That is more easy. Great.

8

ilyas YILDIZHAN

[='{ENTITY NAME}'.replace(/ (.*)/g,"")] ilyas

[='{ENTITY NAME}'.replace(/(.*) /g,"")] YILDIZHAN

[='{ENTITY NAME}'.replace(/ (.*)/g,"")] [='{ENTITY NAME}'.replace(/(.*) /g,"")] ilyas YILDIZHAN

3 Likes
9

Thanks Ilyas, that worked a treat! :+1::+1:

I had to use this though:
[='{ENTITY NAME:Customers}'.replace(/ (.*)/g,"")]

1 Like
10

Hie… how do i extract the LastName for example if its John Steward Michaels, i was using [=‘{ENTITY NAME}’.replace(/(.*) /g,“”)] but it gave me only Michaels not the full LastName :Steward Michaels

and also some of my customer entity names have phone numbers how do i exclude the phone numbers at the end.

11

I had to join the names, so either:

StewartMichaels or

Stewart_Michaels.

12

I think the code takes (going backwards) the characters up to a ‘space’. Can you put the phone number before the name?

There’s probably code that can do what you need for both issues but I don’t know it sorry :pensive_face:

Hope this helps :+1:

13

i ended up using an sql script to split the name and remove any non letter characters and ignore Mr.Mrs,Miss and Dr….i hope its the best choice and it wont lag my system

if you are good in sql pls check for me to see if this sql is the right one

{REPORT SQL DETAILS:SELECT e.[Name] AS FullName, LEFT(CleanedName, CHARINDEX(' ', CleanedName + ' ') - 1) AS FirstName, NULLIF(LTRIM(SUBSTRING(CleanedName, CHARINDEX(' ', CleanedName), LEN(CleanedName))), '') AS LastName FROM Entities e CROSS APPLY (SELECT CASE WHEN LEFT(CleanedRaw, CHARINDEX(' ', CleanedRaw + ' ') - 1) IN ('Mr','Mrs','Miss','Dr') THEN LTRIM(SUBSTRING(CleanedRaw, CHARINDEX(' ', CleanedRaw + ' '), LEN(CleanedRaw))) ELSE CleanedRaw END AS CleanedName FROM (SELECT STRING_AGG(c, '') WITHIN GROUP (ORDER BY n) AS CleanedRaw FROM (SELECT SUBSTRING(e.[Name], v.number, 1) AS c, v.number AS n FROM master..spt_values v WHERE v.type = 'P' AND v.number BETWEEN 1 AND LEN(e.[Name]) AND SUBSTRING(e.[Name], v.number, 1) LIKE '[A-Za-z ]') AS chars) AS CleanedRawTable) AS Cleaned WHERE e.EntityTypeId = (SELECT Id FROM EntityTypes WHERE Name = 'Customers') AND e.[Name] = '{ENTITY NAME:Customers}';:F.FirstName,F.LastName::{0} {1}}

14

How do you know that “Steward” is part of the last name and not a middle name or part of the first name? Handling user input is hard.

I’m not too keen on a round-trip to the db for this with the overhead of processing nvarchar column as JSON.

I would lean on the scripting engine to handle this. Pass the name into a JS function. Something like this:

function getLastString(input) {
  var parts = input.split(" ");

  return parts[parts.length - 1];
}

I would go further and handle leading, trailing spaces, normalise > 1 spaces to 1 space.

15

i will try and use the scripting you gave and see how it goes