0

I have a field that needs to be split at the comma into two new fields, before_comma & after_comma. What regular expression could I use for this?

ASAP,LWBS
FCCTESTING,SBLEVELFOUR
FCCTESTING,CE CRITICAL
ADT,EMERGENCY
FCCTESTING,LTCRITICAL
FCCTESTING,DKLEVELFOUR
FCCTESTING,CG CRITICAL
FCCTESTING,JWLEVELONE
EDTOINPATIENTADMISSION,ONE
TEST,EMERGENCY
user3691608
  • 47
  • 12
  • Why would you use regular expression for something that can be done with just searching for a comma and doing a substring? – James Z Mar 21 '22 at 18:13
  • That is what I was looking for, but I figured that would require something like nested substrings. Plus, I was not sure how to search for a specific character in a substring. I figured regex would be able to do it in one short line. I ended up doing it this way. REVERSE(PARSENAME(REPLACE(REVERSE(myString), ',', '.'), 1)) before, REVERSE(PARSENAME(REPLACE(REVERSE(myString), ',', '.'), 2)) after – user3691608 Mar 21 '22 at 19:03
  • I wouldn't use parsename, since you have to create a kludge by replacing , -> . and possibly break something. And it's not really even easier than just using `substring` and `charindex` – James Z Mar 21 '22 at 19:29
  • What version of SQL Server? Recent versions support https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15 – SOS Mar 22 '22 at 05:43
  • 15.0.4198.2, split_string doesn't work on it. Per advice from James, I changed it to this. REPLACE(SUBSTRING(myString, 1, (CHARINDEX(',', myString))), ',', '') before_comma, SUBSTRING(myString, (CHARINDEX(',', myString) + 1), LEN(myString)) after_comma – user3691608 Mar 22 '22 at 12:13
  • If you mean Server 2019, SPLIT_STRING() definitely works in that version. Try it on dbfiddle. It works from SQL Server 2016+ https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=1948b922aa316de04d637cab6e0f0163 – SOS Mar 22 '22 at 19:05

0 Answers0