CODEWITHSUNDEEP
sql-servert-sql
PicoDeGallo
PicoDeGallo

Reputation: 608

Conditionally format inconsistent user input strings

I have a freeform string field that a user inputs a set of values into. This enables the user to enter information in any manner, but also comes with inconsistencies in formatting. For data cleanup purposes, I would like to format the strings to be standard regardless of how they entered them. For instance, I have the following set of data. Notice how the colon is entered in a variety of formats.

Description
---------------------------
ARCHIVE PERSONNEL FOLDERS : INACTIVE
ACTIVE PERSONNEL FOLDERS: ACTIVE
ACTIVE AUDIT FOLDERS :ACTIVE
UNKNOWN AUDIT FOLDERS:ACTIVE
ABC CLIENT FOLDERS  : INACTIVE
DEF CLIENT FOLDERS :  ACTIVE

I would like to format the string so that the colon is immeditately appended to the word it comes before, and has a single space following. 

Description
---------------------------
ARCHIVE PERSONNEL FOLDERS: INACTIVE
ACTIVE PERSONNEL FOLDERS: ACTIVE
ACTIVE AUDIT FOLDERS: ACTIVE
UNKNOWN AUDIT FOLDERS: ACTIVE
ABC CLIENT FOLDERS: INACTIVE
DEF CLIENT FOLDERS: ACTIVE

What is the best approach to format the data as desired and to account for any manner of incorrect colon formatting (space before, no space, space before and after, two spaces before, etc.)?

Upvotes: 1

Views: 53

Answers (2)

tarheel
tarheel

Reputation: 4797

Assumptions:

I made a couple assumptions when coming up with this answer. There are workarounds to these assumptions if they are not valid, but figured it was best to start with the easiest scenario.

1) There will never be a . entered in the freeform string field.

2) There will never be more than one : included in the freeform string field.

Sample Data Setup:

declare @table table
    (
        description varchar(100) not null
    )

insert into @table
    values  ('ARCHIVE PERSONNEL FOLDERS : INACTIVE')
            , ('ACTIVE PERSONNEL FOLDERS: ACTIVE')
            , ('ACTIVE AUDIT FOLDERS :ACTIVE')
            , ('UNKNOWN AUDIT FOLDERS:ACTIVE')
            , ('ABC CLIENT FOLDERS  : INACTIVE')
            , ('DEF CLIENT FOLDERS :  ACTIVE')

Answer:

Just like Lewis Therin said in the comments to the question, REPLACE is quite useful here. It will help set things up for a outside the documented purpose, but reliable, use of the function called parsename.

The select statement below splits each of the strings into two, based on where the : is, trims off any extra spaces from each of them, and then concatenates them back together with the specified : (colon with space) between them.

select rtrim(ltrim(parsename(replace(t.description, ':', '.'), 2))) + ': ' + rtrim(ltrim(parsename(replace(t.description, ':', '.'), 1))) as description
from @table as t

Results:

Description
-------------------------------------
ARCHIVE PERSONNEL FOLDERS: INACTIVE
ACTIVE PERSONNEL FOLDERS: ACTIVE
ACTIVE AUDIT FOLDERS: ACTIVE
UNKNOWN AUDIT FOLDERS: ACTIVE
ABC CLIENT FOLDERS: INACTIVE
DEF CLIENT FOLDERS: ACTIVE

Upvotes: 1

S3S
S3S

Reputation: 25122

This should work regardless of the number of spaces before and after the colon

declare @table table([Description] varchar(256))
insert into @table
values
('ARCHIVE PERSONNEL FOLDERS : INACTIVE'),
('ACTIVE PERSONNEL FOLDERS: ACTIVE'),
('ACTIVE AUDIT FOLDERS :ACTIVE'),
('UNKNOWN AUDIT FOLDERS:ACTIVE'),
('ABC CLIENT FOLDERS  : INACTIVE'),
('DEF CLIENT FOLDERS :  ACTIVE')

select
    [Description]
    ,rtrim(substring([Description],0,CHARINDEX(':',[Description]))) + ': ' + ltrim(substring([Description],CHARINDEX(':',[Description])+1,len([Description])))
from
    @table

Upvotes: 3

Related Questions