Link to home
Start Free TrialLog in
Avatar of thayduck
thayduckFlag for United States of America

asked on

SQL Trimming

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 =  ?????
Avatar of PortletPaul
PortletPaul
Flag of Australia image

set namedesc = flda + ' - ' +  fldb + ' - ' +  fldc + ' - ' +  fldd
declare @myvar nvarchar(255)
select @myvar = replace(replace(field1 + '-' + field2 + '-' + field3 + '-' + field4, '---', '-'), '--', '-')
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

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 ''.
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

Argh. I guess others posted while I was typing; therefore, my apologies for duplication. Good luck with the solution.
Avatar of thayduck

ASKER

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..
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...
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

mwvisa1:

Trailer Repairs--Z6 I11014RAIL-

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

s/be   Trailer Repairs-Z6 I11014RAIL
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.
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?
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)
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
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 ?