Web design and hosting, database, cloud and social media solutions that deliver business results
  • Business Solutions
    • Robotic Process Automation
    • Bespoke Software
    • Database Services
      • Data Integration
      • Datawarehouse Services
      • Power BI
      • Server Upgrade and DBA Services
    • Web Services
      • Logo Design
      • Payment Gateways
      • Web Localisation and Translation
      • Web Site Optimisation
      • Web Site Security
      • Technical Tools
    • Cloud Services
      • Amazon Web Services
      • Google Cloud Services
      • Microsoft Azure
    • Microsoft 365
      • Enabling the Multi Factor Authentication Application
      • Office 365 DNS Settings Generator
    • IT Hardware
    • Social Media Services
  • Academy
    • Our Test Environment
    • Learning Databases
      • The Basics
      • Get Open Query
      • SQL Server Data
      • SQL Server Maintenance
      • Using SQL Server Dates
      • Using SQL Server Functions
      • Using SQL Server Pivot-Unpivot
      • Technical Tools
    • Learning Web Design
      • Building Ousia Content Management System
      • Using ASP-NET
      • Using CSS
      • Using JavaScript
    • Learning Cloud and IT Services
      • Task Scheduler Error 2147943645
      • Blocking Blank Senders
      • Requesting SSL and Generation of PFX file in OpenSSL Simple Steps
    • Using Social Media
      • Asking for a Google Review
      • Changing a Facebook account from personal to business
      • Choosing where to focus Social Media effort
      • Social Media Image Sizes
      • Using Meta Data to set Social Media Images
  • About
    • Blog
      • Building an entry level gaming machine
      • Google Core Update Jan 2020
      • Hot Chilli Internet Closure
      • How To Choose Content For Your Website Adverts Leaflets
      • Preventing Online Scam
      • Skimmers of the gig economy
      • The most annoying things about websites on the Internet
      • Top 5 websites for free Vector Graphics
    • Careers
      • Translator English-Japanese
      • Translator English-Portuguese
      • Translator English-Spanish
      • Translator English-Turkish
    • Portfolio
    • Regulatory
    • Team
      • Chester Copperpot
      • Gavin Clayton
      • Sai Gangu
      • Suneel Kumar
      • Surya Mukkamala
عربى (AR)čeština (CS)Deutsch (DE)English (EN-US)English (EN-GB)Español (ES)فارسی (FA)Français (FR)हिंदी (HI)italiano (IT)日本語 (JA)polski (PL)Português (PT)русский (RU)Türk (TR)中国的 (ZH)

SQL Server Function for concatenating strings

A flexible and re-usable SQL Server Function to return a formatted and delimited text string from a set of columns or variables

Displaying well formatted text

Using SQL Server Functions

This function was born out of the need to display an address in a well formatted string. When working with this sort of data, there are often blanks, NULL values and sometimes separated text within the data fields. Standard concatenation of the columns led to double delimiters with blank spaces or commas at the end.

With this in mind we needed a way of separating out the data, excluding the blanks, trimming white space, and removing the trailing data. There are various ways of using standard COALESCE alone, but that didn't quite cut it with the blanks.

We used our tried and tested TextToRows function to split and sort the text, then wrote the text values back into a new string. The benefit of this is that the data is re-usable with delimiters of your choice for both input and output.

More: SQL Server function splitting text into data rows

SQL

ALTER FUNCTION dbo.TextConc(@Text NVARCHAR(MAX),@Delim CHAR(1),@Sep CHAR(2)) RETURNS NVARCHAR(MAX) WITH SCHEMABINDING AS BEGINDECLARE @Str NVARCHAR(MAX)SELECT @Str=COALESCE(@Str+@Sep,'')+REPLACE(WordStr,@Delim,'')FROM dbo.TextToRows(@Delim,@Text)WHERE WordStr<>''RETURN REPLACE(@Str,@Sep+@Sep,@Sep)ENDGO

Test Data

For testing purposes we can create a temporary table and store a couple of addresses. Combine the select statement with function and you can see how it returns a crisply formatted data set that is perfect for display purposes.

SQL

DECLARE @Address TABLE(AddressName NVARCHAR(100),AddressL1 NVARCHAR(100),AddressL2 NVARCHAR(100),AddressL3 NVARCHAR(100),AddressCity NVARCHAR(100),AddressCounty NVARCHAR(100),AddressCountry NVARCHAR(100),AddressPostCode NVARCHAR(100))INSERT INTO @AddressSELECT 'Mountain View','1600 Amphitheatre Parkway',NULL,NULL,'Mountain View','California','United States','94043'INSERT INTO @AddressSELECT 'London','1-13 St Giles High St',NULL,NULL,'London','London','United Kingdom','WC2H 8LG'SELECT dbo.TextConc(ISNULL(AddressName,'')+','+ISNULL(AddressL1,'')+','+ISNULL(AddressL2,'')+','+ISNULL(AddressL3,'')+','+ISNULL(AddressCity,'')+','+ISNULL(AddressCounty,'')+','+ISNULL(AddressCountry,'')+','+ISNULL(AddressPostCode,''),',',', ')FROM @Address

Results

Mountain View, 1600 Amphitheatre Parkway, Mountain View, California, United States, 94043
London, 1-13 St Giles High St, London, London, United Kingdom, WC2H 8LG

Persist the data in the table

As we created the data with SCHEMABINDING, we can also append it to a table as a computed column. 

Note, when adding these, it can impact query performance, so we generally try to keep the data persisted, this actually stores the data in the table, and it is only calculated on insert/update transactions.

SQL

CREATE TABLE TestAddress(AddressName NVARCHAR(100),AddressL1 NVARCHAR(100),AddressL2 NVARCHAR(100),AddressL3 NVARCHAR(100),AddressCity NVARCHAR(100),AddressCounty NVARCHAR(100),AddressCountry NVARCHAR(100),AddressPostCode NVARCHAR(100),AddressDisplay as dbo.TextConc(ISNULL(AddressName,'')+','+ISNULL(AddressL1,'')+','+ISNULL(AddressL2,'')+','+ISNULL(AddressL3,'')+','+ISNULL(AddressCity,'')+','+ISNULL(AddressCounty,'')+','+ISNULL(AddressCountry,'')+','+ISNULL(AddressPostCode,''),',',', ') PERSISTED)INSERT INTO TestAddressSELECT 'Mountain View','1600 Amphitheatre Parkway',NULL,NULL,'Mountain View','California','United States','94043'INSERT INTO TestAddressSELECT 'London','1-13 St Giles High St',NULL,NULL,'London','London','United Kingdom','WC2H 8LG'SELECT * FROM TestAddressDROP TABLE TestAddress

Author

Was this helpful?

Please note, this commenting system is still in final testing.
Copyright Claytabase Ltd 2020

Registered in England and Wales 08985867

Site Links

RSSLoginLink Cookie PolicySitemap

Social Media

facebook.com/Claytabaseinstagram.com/claytabase/twitter.com/Claytabaselinkedin.com/company/claytabase-ltd

Get in Touch

+442392064871info@claytabase.co.ukClaytabase Ltd, Unit 3d, Rink Road Industrial Estate, PO33 2LT, United Kingdom

Partnered With

The settings on this site are set to allow all cookies. These can be changed on our Cookie Policy & Settings page.
By continuing to use this site you agree to the use of cookies.
Ousia Logo
Logout
Ousia CMS Loader