Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Trimming

Posted on 2013-05-21
16
Medium Priority
?
184 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 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 70

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

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.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

886 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