Solved

SQL Trimming

Posted on 2013-05-21
16
170 Views
Last Modified: 2013-05-21
flda               fldb               fldc               fldd

joe                                      john              bob
                      sue                                     carol
bill                                                            bob
harry             hana             hank             hal
terry
                                            julie

I need to read the above records from a sql table (tablea) and update a field called namedesc in the same table using a set statement, unless there is a better way.

I need the namedesc field to look like below.


joe-john-bob
sue-carol
bill-bob
harry-hana-hank-hal
terry
julie


set namedesc =  ?????
0
Comment
Question by:thayduck
  • 6
  • 3
  • 3
  • +3
16 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
set namedesc = flda + ' - ' +  fldb + ' - ' +  fldc + ' - ' +  fldd
0
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
declare @myvar nvarchar(255)
select @myvar = replace(replace(field1 + '-' + field2 + '-' + field3 + '-' + field4, '---', '-'), '--', '-')
0
 
LVL 20

Expert Comment

by:dsacker
Comment Utility
I included a SELECT statement so you could see the results beforehand. Tested this through a temp table. The trick is concatenating the hyphen WITHIN the ISNULL.
DECLARE @MyTable TABLE
    (   flda        varchar(10),
        fldb        varchar(10),
        fldc        varchar(10),
        fldd        varchar(10),
        namedesc    varchar(40) )

INSERT INTO @MyTable (flda, fldb, fldc, fldd)
SELECT 'Joe',    NULL,  'John', 'Bob'       UNION ALL
SELECT  NULL,   'Sue',   NULL,  'Carol'     UNION ALL
SELECT 'Bill',   NULL,   NULL,  'Bob'       UNION ALL
SELECT 'Harry', 'Hana', 'Hank', 'Hal'       UNION ALL
SELECT 'Terry',  NULL,   NULL,  'Julie'

SELECT  ISNULL(flda + '-', '') +
            ISNULL(fldb + '-', '') +
            ISNULL(fldc + '-', '') +
            ISNULL(fldd, '') AS Name
FROM   @MyTable

UPDATE  @MyTable
SET     namedesc = ISNULL(flda + '-', '') +
                   ISNULL(fldb + '-', '') +
                   ISNULL(fldc + '-', '') +
                   ISNULL(fldd, '')

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Hi.

Are those individual columns (i.e., flda, fldb, fldc, and fldd)? If yes, then you can use ISNULL() or COALESCE() to replace NULL values with an empty string, so you can concatenate the four fields like this.

WITH your_table(flda, fldb, fldc, fldd) AS (
    SELECT 'joe', NULL, 'john', 'bob' UNION
    SELECT NULL, 'sue', NULL, 'carol' UNION
    SELECT 'bill', NULL, NULL, 'bob' UNION
    SELECT 'harry', 'hana', 'hank', 'hal' UNION
    SELECT 'terry', NULL, NULL, NULL UNION
    SELECT NULL, NULL, 'julie', NULL
)
SELECT namedesc = STUFF(ISNULL('-'+flda, '')+ISNULL('-'+fldb, '')+ISNULL('-'+fldc, '')+ISNULL('-'+fldd, ''), 1, 1, '')
FROM your_table
;

Open in new window


To avoid complicated checks for field values to left or right, I opted to make every value have an extra '-' in front then just took STUFF to replace first character with ''.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
set namedesc = 
  substring(
  case when flda is not null then '-' + flda else '' end
+ case when fldb is not null then '-' + fldb else '' end
+ case when fldc is not null then '-' + fldc else '' end
+ case when fldd is not null then '-' + fldd else '' end
    , 2, 800)

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Argh. I guess others posted while I was typing; therefore, my apologies for duplication. Good luck with the solution.
0
 

Author Comment

by:thayduck
Comment Utility
lsvidge:

Projects - Bridges-AG-        You are leaving a trailing -   S/be        Projects - Bridges-AG
when there is only one field with data.

dsacker:

Terry-                                  You are leaving a trailing -   S/be        Terry
when there is only one field with data.

 Testing other solutions..
0
 

Author Comment

by:thayduck
Comment Utility
PortletPaul:


Projects --AA M-0321.050-    You have 2 -- between fields and also have a trailing -.

should be      Projects -AA M-0321.050

above is actual data from table...
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 20

Expert Comment

by:dsacker
Comment Utility
Added an extra update to remove any trailing hyphens:
DECLARE @MyTable TABLE
    (   flda        varchar(10),
        fldb        varchar(10),
        fldc        varchar(10),
        fldd        varchar(10),
        namedesc    varchar(40) )

INSERT INTO @MyTable (flda, fldb, fldc, fldd)
SELECT 'Joe',    NULL,  'John', 'Bob'       UNION ALL
SELECT  NULL,   'Sue',   NULL,  'Carol'     UNION ALL
SELECT 'Bill',   NULL,   NULL,  'Bob'       UNION ALL
SELECT 'Harry', 'Hana', 'Hank', 'Hal'       UNION ALL
SELECT 'Terry',  NULL,  'Julie', NULL

UPDATE  @MyTable
SET     namedesc = ISNULL(flda + '-', '') +
                   ISNULL(fldb + '-', '') +
                   ISNULL(fldc + '-', '') +
                   ISNULL(fldd, '')

UPDATE  @MyTable
SET     namedesc = LEFT(namedesc, LEN(namedesc) - 1)
WHERE   RIGHT(namedesc, 1) = '-'

SELECT * FROM @MyTable

Open in new window

0
 

Author Comment

by:thayduck
Comment Utility
mwvisa1:

Trailer Repairs--Z6 I11014RAIL-

You are also putting in 2 -- bewteen fields and also have a trailing -.

s/be   Trailer Repairs-Z6 I11014RAIL
0
 

Author Comment

by:thayduck
Comment Utility
dsacker:

Lease of Asset--ZJ      s/be    Lease of Asset-ZJ

Still have 2 dashes between fields, Should only be on 1.

You did remove trailing dash.
0
 
LVL 20

Expert Comment

by:dsacker
Comment Utility
Did you put the ISNULL(fldx + '-', '') exactly as demonstrated. When a field is null, that should eliminate the duplicate hyphen.

Are you setting a fldx value to '' (nothing within single quotes) instead of NULL?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 150 total points
Comment Utility
I get:

julie
sue-carol
bill-bob
harry-hana-hank-hal
joe-john-bob
terry

from:
select
  substring(
  case when flda is not null then '-' + flda else '' end
+ case when fldb is not null then '-' + fldb else '' end
+ case when fldc is not null then '-' + fldc else '' end
+ case when fldd is not null then '-' + fldd else '' end
    , 2, 800)
from your_table

Open in new window


not sure how you are getting double -- from this

can we get some actual data?

also see: http://sqlfiddle.com/#!3/1fa93/7097
{edit added url and code bock}
0
 

Author Comment

by:thayduck
Comment Utility
PortletPaul:


I saw that some of the fields have blanks and some have nulls.
I made a slight change to your code and now it does not put in 2 dashes.
Does just what I want it to.

Is there a better way to do this when checking for blanks or nulls:  ?

case when #ARMast_Desc.BillGroupDesc > '' or #ARMast_Desc.BillGroupDesc is null then '-' + #ARMast_Desc.BillGroupDesc else '' end



But your below code does work when I also check for blanks.


     set #ARMast_Desc.InvDes =
  substring(
  case when #ARMast_Desc.BillGroupDesc > '' or #ARMast_Desc.BillGroupDesc is null then '-' + #ARMast_Desc.BillGroupDesc else '' end
+ case when #ARMast_Desc.FlexField15 > '' or #ARMast_Desc.FlexField15 is null then '-' + #ARMast_Desc.FlexField15 else '' end
+ case when #ARMast_Desc.ZUONR > '' or #ARMast_Desc.ZUONR is null then '-' + #ARMast_Desc.ZUONR else '' end
+ case when #ARMast_Desc.SGtxt > '' or #ARMast_Desc.SGtxt is null then '-' + #ARMast_Desc.SGtxt else '' end
    , 2, 800)
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
SET namedesc =
    CASE WHEN flda > '' THEN flda
        ELSE '' END +
    CASE WHEN fldb > '' THEN CASE WHEN flda > '' THEN '-' ELSE '' END + fldb
        ELSE '' END +
    CASE WHEN fldc > '' THEN CASE WHEN flda > '' OR fldb > '' THEN '-' ELSE '' END + fldc
        ELSE '' END +
    CASE WHEN fldd > '' THEN CASE WHEN flda > '' OR fldb > '' OR fldc > '' THEN '-' ELSE '' END + fldd
        ELSE '' END



Or, using the column names above:


    CASE WHEN #ARMast_Desc.BillGroupDesc > '' THEN #ARMast_Desc.BillGroupDesc
        ELSE '' END +
    CASE WHEN #ARMast_Desc.FlexField15 > '' THEN CASE WHEN #ARMast_Desc.BillGroupDesc > '' THEN '-' ELSE '' END + #ARMast_Desc.FlexField15
        ELSE '' END +
    CASE WHEN #ARMast_Desc.ZUONR > '' THEN CASE WHEN #ARMast_Desc.BillGroupDesc > '' OR #ARMast_Desc.FlexField15 > '' THEN '-' ELSE '' END + #ARMast_Desc.ZUONR
        ELSE '' END +
    CASE WHEN #ARMast_Desc.SGtxt > '' THEN CASE WHEN #ARMast_Desc.BillGroupDesc > '' OR #ARMast_Desc.FlexField15 > '' OR #ARMast_Desc.ZUONR > '' THEN '-' ELSE '' END + #ARMast_Desc.SGtxt
        ELSE '' END
0
 

Author Closing Comment

by:thayduck
Comment Utility
Thanks for everyone's  help.

I might have some followup questions about this.

dsacker solution also works but had 1 extra update to table.

Not sure why if I pick multiple solutions I have to award 20 points to everyone that had a suggestion ?
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now