Skip to content
Donner's Daily Dose of Drama
Donner's Daily Dose of Drama
  • The Good
    • Blogging
    • Consumer Protection
    • Environment
    • Ethics
    • Geek’s Home
    • Lisa Lanett
    • Medfield
    • Music
    • Parenting and Technology
    • Travel
    • wow
  • The Bad
    • Business
    • Ebay
    • Investment
    • Job search
    • Personal Finance
    • Politics
  • The Ugly
    • Information Technology
      • Business Intelligence
      • Content Management
      • Free Software
      • I18N and L10N
      • Java
      • Open Source
      • Mobile Devices
      • Open Source Business Intelligence
      • OSBI
      • SDA
      • Security
      • Smartphone
      • Software Best Practices
      • Software Engineering
      • SQL Server
      • Streaming Media
      • Web
    • Austria
    • Fiction
    • Hardware
    • iPod
    • Miscellaneous
    • Uncategorized
    • Video
    • Weekend Warrior
Donner's Daily Dose of Drama

T-SQL: replace occurrences of two or more characters in a row with a single new character

Christian Donner, May 31, 2013May 31, 2013

Here is a T-SQL UDF that replaces occurrences of two or more characters in a row with a single new character, a delimiter.

CREATE FUNCTION dbo.collapsecharsequence 
(
    @fulldata nvarchar(max), 
    @char nchar(1), @delim nchar(1)
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @i int = 1;
    WHILE @i < LEN(@fulldata)-1
    BEGIN
        IF (SUBSTRING(@fulldata, @i, 1) IN (@char, @delim) 
            AND SUBSTRING(@fulldata, 1+@i, 1) = @char)
            SET @fulldata = STUFF(@fulldata, @i, 2, @delim);
        ELSE
            SET @i = @i + 1;
    END
    RETURN @fulldata;
END

I use this function in a label data parsing application for collapsing spaces found in data fields into a single delimiter.
For example:

select dbo.collapsecharsequence('null                   eins  zwei drei|vier', ' ', '|');

returns this:

null|eins|zwei drei|vier

I consider a single space as part of the data value, but multiple spaces indicate that there are actually two separate data elements that I want to treat individually.

Related Posts:

  • OpenVPN
  • The Voip.ms SMS Integration for Home Assistant
  • Enphase Envoy Local Access
  • TyreWiz not working after battery change
  • The Great Cat Litter Poop Off

Software Engineering SQL Server delimiterfunctionreplacestringT-SQL

Post navigation

Previous post
Next post

Leave a Reply

Your email address will not be published. Required fields are marked *

Pages

  • About
  • Awards
    • TechnoLawyer
  • Contact Christian Donner
  • Project Portfolio
  • Publications
  • Speaking Engagements

Recent Comments

  • Christian Donner on Sealing a leaky cast-iron fireplace chimney damper
  • Eric on Sealing a leaky cast-iron fireplace chimney damper
  • Christian Donner on Contact Christian Donner
  • Max on Contact Christian Donner
  • Christian Donner on Contact Christian Donner

Tags

AHCI Amazon Android ASP.Net AT&T Droid Drupal email Error failure featured firmware Garmin Godaddy Google honda Internet Explorer 8 iPhone Lenovo Lisa Lanett Modules NAS Nexus One Paypal Performance Privacy QNAP raid RS-407 sauna Security spam SQL SR3600 Synology T-Mobile T430s transmission tylö Verizon Virus VMWare Windows 7 windows 8.1 Windows Mobile
  • About
  • Awards
    • TechnoLawyer
  • Contact Christian Donner
  • Project Portfolio
  • Publications
  • Speaking Engagements
©2025 Donner's Daily Dose of Drama | WordPress Theme by SuperbThemes