Link to home
Start Free TrialLog in
Avatar of Jasmine Sandlas
Jasmine SandlasFlag for Oman

asked on

Sql Logic together

Please help folks.

How to club this two logics together for a single field. Field name is description.

1.) case when right(rtrim(Description),1) = ',' then substring(rtrim(Description),1,len(rtrim(Description))-1)
2.) replace (replace (Description, char(10), ''), char(13), '') Description,

Please help.

Thanks in advance
Avatar of Bill Prew
Bill Prew

Here's one approach:

case when right(rtrim(replace(replace(Description,char(10),''),char(13),'')),1) = ',' then substring(rtrim(replace(replace(Description,char(10),''),char(13),'')),1,len(rtrim(replace(replace(Description,char(10),''),char(13),'')))-1) Description,

Open in new window


»bp
Avatar of Jasmine Sandlas

ASKER

I checked it doesnot work
field end commas are getting evaded but somehow the char(13) and char(10) logic doesn't work.
do they work separately ?

have you got an actual sample of your data ?

with sample as (
  select 'a silly not really representative sample of a description' description from dual)
select replace (replace (Description, char(10), ''), char(13), '') Description,
from (
  select case when right(rtrim(Description),1) = ',' then substring(rtrim(Description),1,len(rtrim(Description))-1)  end description
  from sample)

Open in new window

Did it work individually before?  Meaning, if you just do this does it remove those characters?

Replace(Replace(Description,Char(10),''),Char(13),'') Description

Open in new window


»bp
the case probably doesn't work ...

you are missing an "end"
yes Bill it works individually.
I added end to it.
the else part of the case is optional
but what if the description doesn't contain a ',' ?
separately they both are working absolutely fine.
I am testing with those which has ','
why just only test those with ',' ?
do you want to replace the cr lf in the other ones or just the ones with ',' ?

that's important if you nest instructions
replace char for other ones not with ','
so I am testing for both
Yes, END was missing, so should be:

  CASE
    WHEN RIGHT(RTRIM(REPLACE(REPLACE(Description, CHAR(10), ''), CHAR(13), '')), 1) = ',' 
      THEN SUBSTRING(RTRIM(REPLACE(REPLACE(Description, CHAR(10), ''), CHAR(13), '')), 1, LEN(RTRIM(REPLACE(REPLACE(Description, CHAR(10), ''), CHAR(13), ''))) - 1)
  END Description

Open in new window


»bp
ugh ??? drunk, fix later ????
that's what i say when i loose track of someone explaining in "nuclear science fashion" a simple problem

the ones ending with ',' , you want without the ','
the cr lf is replaced when exactly ?
Okay, will get back to you on this. thanks
need to check data more on this
Okay, let us know...


»bp
char(10) and char(13) logic we are using for carriage return and line feed, because the field has some tabs and we are correcting it by this logic and making it present in a single tab. it is different logic

now we need to add one more logic to remove the last end commas for the same field.

These two logic are working fine separately but I need to make it collate and do the same thing which is happening individually.

@Bill Prew
@Geert Gruwez
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

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
let me check bill
Awesome bill, this is what I was looking for.. thanks so much for you help.
Welcome, glad that finally worked out.


»bp