Solved

SQL Trimming

Posted on 2013-05-21
16
172 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 59

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 48

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 59

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 48

Accepted Solution

by:
PortletPaul earned 150 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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 Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

816 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

10 Experts available now in Live!

Get 1:1 Help Now