Regex-Like Parsing of Space Separated Entries in SQL Server

Data and Databases
1

Can SQL Server mimic regex to split a space-delimited string into separate columns (Nation, Date, City)? Consider:

entrydata
---------
Canada 12-Dec-2012 Toronto
Brazil 01-Jan-2013 Rio
France 03-Mar-2014 Paris
2

hey, i really like ur idea! i too played with recursive cte’s before and saw some quirks with inexact spacings. did you ever try dynamic sql for that? curious how u handled irregular spacing cases.

3

hey, ive also tried splitting with substring and charindex, and honestly its pretty fast. dynamic sql can kick in if u need more flexibility but watch out for edge cases. sometimes a simple udf just does the trick. good luck with ur solution!

4

SQL Server does not natively support regular expressions in the way languages like Perl or Python do. From personal experience, when needing to split a space-delimited string into separate columns, I found that crafting a custom function using substring and charindex functions works well. Although this approach is a bit verbose, it reliably extracts sections of the string. In more modern versions, you may also explore XML methods or the STRING_SPLIT function, although that is typically more suited for splitting values into rows rather than columns.