?
Solved

SQL Trimming

Posted on 2013-05-21
16
Medium Priority
?
181 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +3
16 Comments
 
LVL 49

Expert Comment

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

Expert Comment

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

Expert Comment

by:dsacker
ID: 39184483
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39184495
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 49

Expert Comment

by:PortletPaul
ID: 39184501
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 60

Expert Comment

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

Author Comment

by:thayduck
ID: 39184583
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
ID: 39184638
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
 
LVL 20

Expert Comment

by:dsacker
ID: 39184664
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
ID: 39184687
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
ID: 39184747
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
ID: 39184755
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 49

Accepted Solution

by:
PortletPaul earned 600 total points
ID: 39184772
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
ID: 39184956
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:Scott Pletcher
ID: 39185030
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
ID: 39185134
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 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