Answers.Body: <p>I've put up a <a href="https://github.com/civicrm/civicrm-core/pull/26549" rel="nofollow noreferrer">PR</a> to make <code>LEFT()</code>, <code>RIGHT()</code> and <code>SUBSTRING()</code> available.  If you don't want to wait for the release with these, just put a copy of those files into the right place.</p>
  <p>That will let you show the truncated string in SearchKit but you can't add the <code>Group By</code> directly although SearchKit <em>does</em> understand it.  So ...</p>
  <ol>
  <li><p>Set up the rest of your search and add a Group By using the relevant <code>postal_code</code> (ignore the truncation part for now)</p>
  </li>
  <li><p>Save your search, go to the main search listing and click <code>Export</code>.</p>
  </li>
  <li><p>Use the <code>Copy to clipboard</code> button</p>
  </li>
  <li><p>Close the pop-up, and click <code>Import</code> on the Saved Searches listing.</p>
  </li>
  <li><p>In the pop-up, paste what you just exported.</p>
  </li>
  <li><p>Go the <code>groupBy</code> section and change <code>address_primary.postal_code</code> to <code>LEFT(address_primary.postal_code, 4)</code></p>
  </li>
  </ol>
  <p><a href="https://i.sstatic.net/dm3W5.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/dm3W5.png" alt="enter image description here" /></a></p>
  <ol start="7">
  <li><p>Finish with <code>Import</code></p>
  </li>
  <li><p>Now open the search.  You should see the grouping correctly displayed eg:</p>
  </li>
  </ol>
  <p><a href="https://i.sstatic.net/BDhIJ.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/BDhIJ.png" alt="enter image description here" /></a></p>
  <p>... and the results should be grouped as you want. :-)</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-18T15:00:54.380Z
Answers.Text: Amazing, thank you for the instructions. Only two slight comments - when I imported it, it overwrote the one that used to export/copied from and I wasn't expecting that, although it makes perfect sense. I'm just stating it here in case anyone is following this and wants to keep a separate copy of before and after. Secondly, the agreggation is working but the label in my Postcode column still shows full postcodes - should I be able to use LEFT in the field transformation (it's not listed)?
Answers.UserDisplayName: Rose Lanigan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-18T18:07:08.140Z
Answers.Text: 1) Good catch about overwriting  2) yes, the `LEFT` in the Group By is separate from any displayed columns, so if you want to display the postcode, also add a `LEFT` to it, but you can do that in the field transformation part of the GUI
Answers.UserDisplayName: Aidan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-17T17:43:14.360Z
Answers.LastActivityDate: 2023-06-17T17:43:14.360Z
Answers.UserDisplayName: Aidan
Answers.Body: <p>Civi provides a smarty modifier that's the same as php substr, so if you add a search display to your searchkit you can check the box for rewrite on the field and use that modifier, like:</p>
  <p><code>{'[postal_code]'|substring:0:4}</code></p>
  <p>The single quotes are important.</p>
  <p>The exact token depends your query. It might be something like <code>[Address.postal_code]</code>. You can use the <code>&lt;/&gt;</code> dropdown to insert it.</p>
  <p>P.S. I am seeing some strangeness though. It works for <code>1:4</code>, but not <code>0:4</code>. Odd.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-14T18:20:24.680Z
Answers.Text: This was my first thought too, but I think the asker is trying to aggregate by the first four digits, which would have to happen in the search itself, I think, rather than the display.
Answers.UserDisplayName: Lars SG
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-14T18:32:15.467Z
Answers.Text: Ok if that's the case then perhaps core would accept adding LEFT as an option: https://github.com/civicrm/civicrm-core/tree/51ec12f5c890cce5fecf393d60d5a3921aa9eff6/Civi/Api4/Query. It seems generally useful.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-14T20:13:00.527Z
Answers.Text: Agreed, seems like a good addition to me.
Answers.UserDisplayName: Lars SG
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-16T08:39:25.167Z
Answers.Text: Thank you both for the replies. Yes, it would need to be in the search so it would be great if that could be added as an option!
Answers.UserDisplayName: Rose Lanigan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-16T16:46:48.560Z
Answers.Text: There's a [PR](https://github.com/civicrm/civicrm-core/pull/26549) up to add it but now that I'm looking at it, for this specific use case I'm not sure it will help. I don't see where you can group-by on a transformed field in searchkit.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-14T17:45:58.417Z
Answers.LastActivityDate: 2023-06-14T17:51:47.920Z
Answers.LastEditDate: 2023-06-14T17:51:47.920Z
Answers.UserDisplayName: Demerit
Body: <p>I would like to run a report of UK postcodes with aggregation. The first four characters of the postcode give an area of perhaps a few square miles, the last three characters are specific to a street so not very helpful.</p>
  <p>Is it possible to do this in SearchKit by rewriting the text or using the field transformation to do this? I did wonder about using the Data Segmentation but we can have up to 70 different variations (and this isn't always predictable) so this would have to be perhaps 40-50 of the most common ones and then 'Other' as a catch all. Setting up the criteria for 'Other' could also be problematic if we have to say != one of 40 other defined patterns.</p>
  <p>We currently use Views with some simple raw SQL :</p>
  <blockquote>
  <p>Left(civicrm_address.postal_code,4)</p>
  </blockquote>
  <p>...but a colleague has spotted that the numbers don't add up properly. I can try to troubleshoot that but I'd rather recreate it in SearchKit if it's possible.</p>
  <p><em>(If you're interested, the issue in Views is that the subtotal appears to be correct and matches a search of contacts in the main database, but if I copy and paste the list into Excel and run a SUM formula, it's about 5% out).</em></p>
  
Tags: <drupal-views><searchkit>
Title: SearchKit - can I return the first four characters of a string?
ThreadText: # SearchKit - can I return the first four characters of a string?
  
  (Asked by: username_0 on 2023-06-14)
  
  I would like to run a report of UK postcodes with aggregation. The first four characters of the postcode give an area of perhaps a few square miles, the last three characters are specific to a street so not very helpful.
  
  Is it possible to do this in SearchKit by rewriting the text or using the field transformation to do this? I did wonder about using the Data Segmentation but we can have up to 70 different variations (and this isn't always predictable) so this would have to be perhaps 40-50 of the most common ones and then 'Other' as a catch all. Setting up the criteria for 'Other' could also be problematic if we have to say != one of 40 other defined patterns.
  
  We currently use Views with some simple raw SQL :
  
  > Left(civicrm\_address.postal\_code,4)
  
  ...but a colleague has spotted that the numbers don't add up properly. I can try to troubleshoot that but I'd rather recreate it in SearchKit if it's possible.
  
  *(If you're interested, the issue in Views is that the subtotal appears to be correct and matches a search of contacts in the main database, but if I copy and paste the list into Excel and run a SUM formula, it's about 5% out).*
  
  **Tags:** drupal-views, searchkit
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2023-06-17. Score: 4
  
  I've put up a [PR](https://github.com/civicrm/civicrm-core/pull/26549) to make `LEFT()`, `RIGHT()` and `SUBSTRING()` available. If you don't want to wait for the release with these, just put a copy of those files into the right place.
  
  That will let you show the truncated string in SearchKit but you can't add the `Group By` directly although SearchKit *does* understand it. So ...
  
  1. Set up the rest of your search and add a Group By using the relevant `postal_code` (ignore the truncation part for now)
  2. Save your search, go to the main search listing and click `Export`.
  3. Use the `Copy to clipboard` button
  4. Close the pop-up, and click `Import` on the Saved Searches listing.
  5. In the pop-up, paste what you just exported.
  6. Go the `groupBy` section and change `address_primary.postal_code` to `LEFT(address_primary.postal_code, 4)`
  
  [![enter image description here](https://i.sstatic.net/dm3W5.png)](https://i.sstatic.net/dm3W5.png)
  
  7. Finish with `Import`
  8. Now open the search. You should see the grouping correctly displayed eg:
  
  [![enter image description here](https://i.sstatic.net/BDhIJ.png)](https://i.sstatic.net/BDhIJ.png)
  
  ... and the results should be grouped as you want. :-)
  
  > **Comments:**
  > **username_0**: Amazing, thank you for the instructions. Only two slight comments - when I imported it, it overwrote the one that used to export/copied from and I wasn't expecting that, although it makes perfect sense. I'm just stating it here in case anyone is following this and wants to keep a separate copy of before and after. Secondly, the agreggation is working but the label in my Postcode column still shows full postcodes - should I be able to use LEFT in the field transformation (it's not listed)?  
  > **username_1**: 1) Good catch about overwriting  2) yes, the `LEFT` in the Group By is separate from any displayed columns, so if you want to display the postcode, also add a `LEFT` to it, but you can do that in the field transformation part of the GUI  
  
  ---
  
  ### Answer by username_2 on 2023-06-14. Score: 0
  
  Civi provides a smarty modifier that's the same as php substr, so if you add a search display to your searchkit you can check the box for rewrite on the field and use that modifier, like:
  
  `{'[postal_code]'|substring:0:4}`
  
  The single quotes are important.
  
  The exact token depends your query. It might be something like `[Address.postal_code]`. You can use the  dropdown to insert it.
  
  P.S. I am seeing some strangeness though. It works for `1:4`, but not `0:4`. Odd.
  
  > **Comments:**
  > **username_3**: This was my first thought too, but I think the asker is trying to aggregate by the first four digits, which would have to happen in the search itself, I think, rather than the display.  
  > **username_2**: Ok if that's the case then perhaps core would accept adding LEFT as an option: https://github.com/civicrm/civicrm-core/tree/51ec12f5c890cce5fecf393d60d5a3921aa9eff6/Civi/Api4/Query. It seems generally useful.  
  > **username_3**: Agreed, seems like a good addition to me.  
  > **username_0**: Thank you both for the replies. Yes, it would need to be in the search so it would be great if that could be added as an option!  
  > **username_2**: There's a [PR](https://github.com/civicrm/civicrm-core/pull/26549) up to add it but now that I'm looking at it, for this specific use case I'm not sure it will help. I don't see where you can group-by on a transformed field in searchkit.  
  
  ---
  
  