In SQL, “split part” usually means extracting one field from a string separated by a delimiter, such as getting the second word from red,green,blue. The exact function depends on your database: PostgreSQL has split_part, while SQL Server, MySQL, Oracle, and SQLite use different approaches.
What “split part” means in SQL
A delimited string contains values separated by a character or text sequence. Common examples include a comma-separated list, a path divided by slashes, or a code with sections separated by hyphens. To split a string and return one part, you need to tell SQL the source string, the delimiter, and which part you want.
For example, splitting red,green,blue on commas gives three fields: red, green, and blue. Asking for part two should return green. A delimiter is not necessarily a single character: in north::central::south, the separator is the two-character sequence ::.
“Split part” is not one universal SQL command. It may refer specifically to PostgreSQL’s split_part function, or more generally to the task of extracting a field. Check which database engine and version your application uses before copying a query.
PostgreSQL: use split_part
PostgreSQL provides a direct function for this task. Its basic form is split_part(string, delimiter, field_number). Field numbers start at 1, so 1 returns the first field and 2 returns the second.
SELECT split_part('red,green,blue', ',', 2);
The result is green. You can use a column instead of a literal, for example:
SELECT split_part(product_code, '-', 2) AS middle_section
FROM products;
If product_code contains AB-472-X, the result for that row is 472. PostgreSQL also accepts a negative field number in modern versions: a negative number counts from the end, so -1 requests the final field. This can be useful when the number of sections varies, but check the behavior against the PostgreSQL version in your environment.
If the requested positive field is beyond the number of fields, PostgreSQL returns an empty string rather than raising an error. That is different from some developers’ expectations, so account for it if an empty result has a special meaning in your application.
SQL Server: split rows, then select the one you need
SQL Server does not have a built-in function named split_part. Its STRING_SPLIT function turns a delimited string into rows. You can filter those rows to find a particular field, but ordering matters: without an ordinal column, the returned row order is not guaranteed.
STRING_SPLIT is available in SQL Server when the database compatibility level is at least 130. In SQL Server 2022 and supported Azure SQL services, its optional third argument can request an ordinal column. With the ordinal available, this query asks for the second field:
SELECT value
FROM STRING_SPLIT('red,green,blue', ',', 1)
WHERE ordinal = 2;
The result is green. The third argument must be a constant 0 or 1; use 1 to include ordinals. SQL Server 2022 and supported Azure offerings are not interchangeable with every older SQL Server installation, so check the product version and compatibility requirements. On older versions, a reliable positional extraction may need a custom function, a JSON conversion, or a different approach.
For a single known field, SQL Server’s CHARINDEX and SUBSTRING can extract text between delimiters, but the expression can become hard to read and must handle missing delimiters carefully. For repeated parsing, a row-producing split method or a normalized table is often easier to maintain.
MySQL: extract a field with SUBSTRING_INDEX
MySQL has no built-in function called split_part, but SUBSTRING_INDEX can be nested to isolate a field. The function returns text on one side of a specified number of delimiter occurrences. A positive count works from the left; a negative count works from the right.
SELECT SUBSTRING_INDEX(
SUBSTRING_INDEX('red,green,blue', ',', 2),
',',
-1
) AS second_part;
The inner call returns red,green, the text through the second comma. The outer call takes the last comma-delimited section of that result, which is green. This pattern works for a known position, but it is less self-explanatory than PostgreSQL’s direct function. If you use it in a query, consider adding a comment or wrapping it in a clearly named expression.
For the last field, SUBSTRING_INDEX(value, ',', -1) is simpler. For a middle field, nesting is usually necessary. Be especially careful when the delimiter can appear inside a field: a simple delimiter-based expression cannot tell whether that character is data or a separator unless the input format defines an escaping or quoting rule.
Oracle and SQLite approaches
Oracle does not provide a function named split_part. One common approach for a simple delimiter-separated string is REGEXP_SUBSTR, using a regular expression that matches characters other than the delimiter:
SELECT REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, 2) AS second_part
FROM dual;
This returns green. The occurrence argument, 2, selects the second match. However, the pattern [^,]+ matches one or more non-comma characters, so it skips empty fields. If empty fields are meaningful, such as the middle value in red,,blue, this pattern does not preserve their positions. A different pattern or a dedicated parsing method is needed.
SQLite has no built-in general-purpose split function. For a one-off task with a simple, known format, expressions using instr and substr can find a delimiter and take part of the string. For multiple fields, the expression quickly becomes cumbersome; a recursive common table expression or application-side parsing may be more appropriate. Choose an approach that explicitly handles missing separators and empty fields rather than assuming every input is perfectly formed.
Delimiters, positions, and edge cases
Before choosing an expression, decide what the data should mean in less tidy cases. Functions may differ in how they treat absent fields, repeated delimiters, empty strings, and null values. For example, red,,blue contains an empty second field if adjacent commas are separators. A method that searches only for nonempty text may instead treat blue as the second match.
- Position numbering: PostgreSQL’s
split_partnumbers fields from 1. Do not assume a function in another database uses the same convention. - Missing delimiters: If a string has no delimiter, some methods return the whole string while others produce an empty or null result. Verify the behavior for your chosen function.
- Null input: A null source value generally propagates to a null result in common string functions, but expression composition and database-specific rules still matter.
- Whitespace: Splitting on a comma does not necessarily remove spaces around the resulting field. For values such as
red, green, trim the extracted value if the space is not part of the data. - Delimiter inside a value: If fields can contain commas or quotes, the format needs an escaping or quoting convention. A plain split is not a CSV parser.
Test representative inputs, not just the clean example. Include a missing separator, a repeated separator, an empty field, a null value, and a value with leading or trailing spaces. Decide whether each case should return a value, an empty string, or null, and write the query to match that rule.
Choosing between string splitting and better data design
Extracting a part at query time is reasonable when the source is a fixed-format code, a small imported value, or a string used for display. It is less suitable when each delimited item is a separate fact that users need to search, join, validate, update, or aggregate.
For example, storing several product categories in one comma-separated column makes it difficult to find every row assigned to a particular category or to enforce a foreign key for each category. A relational design would store categories in their own table and connect products to them through a related table. Then each category is a normal value that SQL can filter and join without repeatedly parsing text.
Use a split expression when the format is stable and parsing is genuinely part of the task. Prefer structured columns or related rows when the values represent separate entities or need independent operations. This is not merely a performance choice: normalization makes the meaning and constraints of the data clearer.
Frequently Asked Questions
Does SQL have a split_part function?
PostgreSQL has a function named split_part. SQL Server, MySQL, Oracle, and SQLite do not use that same built-in function name for general string splitting, though each has other techniques.
How do I get the second value from a comma-separated string?
In PostgreSQL, use split_part(value, ',', 2). In SQL Server, use STRING_SPLIT with ordinals where supported and filter for ordinal 2. In MySQL, a nested SUBSTRING_INDEX expression can isolate the second value.
Does split_part start counting at zero or one?
PostgreSQL’s split_part starts at 1: the first field is 1 and the second is 2. Other database functions may have different conventions, so check their documentation rather than translating indexes by assumption.
Can SQL split a string into multiple rows?
Yes, but the method is database-specific. SQL Server’s STRING_SPLIT, for example, returns rows. PostgreSQL also offers string-to-array and array-expansion functions for workflows that need one row per field.
Should comma-separated values be stored in one SQL column?
Usually not when the values need to be searched, joined, validated, or updated individually. Separate rows or related tables are generally easier to query reliably. A delimited string can still be suitable for an external format or a value treated as a single unit.
Related
- SOLIDWORKS Split: How to Divide a Solid Body
- Split Part in SOLIDWORKS: How to Divide and Save Bodies
- Snowflake SPLIT_PART: Syntax, Examples & Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Conditional Formatting for Yes/No Red and GreenYes—Excel can automatically color “Yes” green and “No” red with conditional formatting. For a status list in B2:B100,…
- Add Percentage in Google Sheets: Formulas and FormattingTo display a value as a percentage in Google Sheets, select the cell and choose Format > Number…
- Unable to Insert an Image in Excel? Causes and FixesIf you are unable to insert an image in Excel for Microsoft 365 on Windows, start with Insert…
- Find Duplicate Values in Excel: Step-by-Step GuideTo find duplicate values in Excel, select the cells you want to check, then choose Home > Conditional…
- Schedule Send in Outlook: How It WorksTo schedule an email in Outlook, compose it, open the arrow beside Send, choose Schedule send, and select…
- Basic Cash Flow Template in Excel: Build and Use ItA basic cash flow template in Excel lists when cash is expected to come in and go out,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.