Web log reports need useful components rather than one long address. Splitting a URL in T-SQL separates the host, path, and query values without pretending to decode them.
Choose a Supported Input Shape
URLs have more syntax than a report usually needs. Start by declaring the input contract instead of building a universal parser. This example accepts absolute HTTP-style addresses without user information or unusual authority forms.
I keep the original address beside the derived components during validation. I also test empty paths and missing query strings before applying a parser to logs. Those cases reveal assumptions hidden by a convenient example.
A host is different from an authority that also includes a port. The first method returns the complete authority as HostAndPort. The later regular expression example returns the hostname alone and leaves any numeric port out.
Neither method performs network access or verifies that an address exists. Parsing stored text is a database transformation. A host-shaped value does not become trustworthy because a query extracted it successfully.
Treat addresses as potentially sensitive data. Query strings can contain identifiers or secrets even when the host looks harmless. Keep report output restricted to the parameters the business needs.
Splitting a URL With Character Positions
The first query removes a fragment before looking for the query separator. It then finds the authority boundary and the beginning of the path. The sample uses reserved example input purely as text.
DECLARE @Url nvarchar(2000) =
N'https://example.invalid/catalog/item?campaign=spring%20sale&item=7#details';
WITH Clean AS
(
SELECT LEFT(@Url, CHARINDEX(N'#', @Url + N'#') - 1) AS WithoutFragment
), Pieces AS
(
SELECT LEFT(WithoutFragment,
CHARINDEX(N'?', WithoutFragment + N'?') - 1) AS BaseUrl,
CASE WHEN CHARINDEX(N'?', WithoutFragment) > 0
THEN SUBSTRING(WithoutFragment,
CHARINDEX(N'?', WithoutFragment) + 1, 2000)
ELSE N'' END AS QueryText
FROM Clean
), Positions AS
(
SELECT BaseUrl, QueryText,
CHARINDEX(N'://', BaseUrl) + 3 AS AuthorityStart
FROM Pieces
), Boundaries AS
(
SELECT BaseUrl, QueryText, AuthorityStart,
CHARINDEX(N'/', BaseUrl + N'/', AuthorityStart) AS PathStart
FROM Positions
)
SELECT SUBSTRING(BaseUrl, AuthorityStart,
PathStart - AuthorityStart) AS HostAndPort,
CASE WHEN PathStart <= LEN(BaseUrl)
THEN SUBSTRING(BaseUrl, PathStart, 2000)
ELSE N'/' END AS UrlPath,
QueryText
FROM Boundaries
WHERE AuthorityStart > 3;The appended separators make CHARINDEX return a boundary when a component is absent. The WHERE clause rejects strings with no scheme delimiter. It is still a limited parser rather than complete URL validation.
Keep fragments out of query values because fragments belong to a different component. A question mark inside a fragment must not create a query string. The order of removal therefore matters.
Encoded separators remain part of their stored value. A percent-encoded ampersand does not split a parameter in this representation. Decode first without preserving structure and you can change the meaning of the address.
Extract Components With Regex
REGEXP_SUBSTR is available in SQL Server 2025. Its final group argument returns a captured subexpression instead of the entire match. The examples use explicit positions, occurrences, and flags so the intended match is visible.
DECLARE @Url nvarchar(2000) =
N'https://example.invalid:8443/catalog/item?campaign=spring%20sale&item=7#details';
SELECT
REGEXP_SUBSTR(@Url, N'^([A-Za-z][A-Za-z0-9+.-]*)://', 1, 1, 'c', 1) AS Scheme,
REGEXP_SUBSTR(@Url, N'^[A-Za-z][A-Za-z0-9+.-]*://([^/:?#]+)', 1, 1, 'c', 1) AS HostName,
REGEXP_SUBSTR(@Url, N'^[A-Za-z][A-Za-z0-9+.-]*://[^/?#]+(/[^?#]*)', 1, 1, 'c', 1) AS UrlPath,
REGEXP_SUBSTR(@Url, N'[?&]campaign=([^&#]*)', 1, 1, 'c', 1) AS CampaignValue;The named parameter pattern matches a complete key boundary before campaign. It does not accidentally match another key containing that word. A repeated campaign parameter returns the first occurrence because occurrence is one.
The host pattern intentionally excludes colon characters. Bracketed IPv6 addresses and user information require a different parsing contract. Reject or route those inputs explicitly rather than returning a convincing but incorrect host.
A missing match returns null. Distinguish a missing component from a present but empty value in downstream reports. The character-position method's default slash and the regex method's null path are deliberate, different output policies.
When splitting a URL, regex makes a declared shape easier to read. It does not replace a standards-aware parser for unrestricted input. Test your accepted shapes and retain rejected rows for review.
Turn the Query Into Ordered Rows
STRING_SPLIT with the ordinal argument requires SQL Server 2022 or later. The function also requires database compatibility level 130 or higher. Order by ordinal when the original parameter order matters.
DECLARE @Query nvarchar(2000) = N'campaign=spring%20sale&item=7&empty=&flag';
SELECT s.ordinal,
CASE WHEN p.EqualsAt > 0
THEN LEFT(s.value, p.EqualsAt - 1)
ELSE s.value END AS ParameterName,
CASE WHEN p.EqualsAt > 0
THEN SUBSTRING(s.value, p.EqualsAt + 1, 2000)
ELSE NULL END AS ParameterValue
FROM STRING_SPLIT(@Query, N'&', 1) AS s
CROSS APPLY (VALUES(CHARINDEX(N'=', s.value))) AS p(EqualsAt)
WHERE s.value <> N''
ORDER BY s.ordinal;Split each token on its first equals sign rather than every equals sign. A value can contain additional equals signs. This keeps the remainder attached to the original key.
The example distinguishes an empty value from a key with no equals sign. Decide whether the report needs that distinction. Repeated keys also require an explicit first, last, or all-values policy.
Preserve Encoding and Meaning
T-SQL has no built-in general URL decoder. Percent-encoded spaces remain encoded, and plus signs remain plus signs in these results. Their interpretation depends on the original encoding format.
Do not replace every percent sequence through a few string substitutions. Multi-byte characters and malformed encodings need a complete decoding policy. Partial decoding creates plausible text with silent corruption.
A URL parser can be brief, but the address has not agreed to be brief. Test Unicode, repeated parameters, empty paths, fragments, and rejected authority shapes. Keep unsupported cases separate from clean report rows.
Which component does the report need? Extracting a campaign value does not require displaying every stored query parameter. Narrow output improves both readability and control over sensitive text.
Case handling also needs an explicit rule. Scheme and hostname comparisons commonly ignore case, while path and parameter semantics depend on the application. Do not lowercase the entire stored address as a convenient cleanup.
The regex examples use the case-sensitive flag for the parameter key. Change that flag only when the input contract treats campaign and Campaign as equivalent. A database collation does not automatically supply the regex comparison policy.
Null input requires its own validation path. A missing stored address is different from an address with a missing campaign parameter. Keep those categories separate if the report measures ingestion quality.
A malformed token should not quietly become a trusted dimension value. Save the rejection reason and original input in a restricted review output. That makes parser improvements possible without corrupting the published grouping.
Validate Before Splitting a URL at Scale
Build a small fixture collection with an expected component for every accepted address. Compare both methods where their declared contracts overlap. Check nulls and rejected shapes rather than inspecting only successful rows.
For large logs, persist needed components during controlled ingestion when repeated parsing becomes expensive. Review the storage and indexing trade-off using your workload. A regex applied to every row still performs work for every row.
Splitting a URL remains useful when the accepted input and output policies stay explicit. Preserve the original, retain encoded values, and record rejected shapes. That gives the report meaningful components without hiding the parser's boundaries.
Related reading on this blog: Pulling Values Out of Text With REGEXP_SUBSTR and REGEXP_INSTR and STRING_SPLIT and Its Ordinal Column.
A parsed address is not a decoded address, it is structured text with a defined contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
