Jasmine Sandlas
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(Descriptio n),1,len(r trim(Descr iption))-1 )
2.) replace (replace (Description, char(10), ''), char(13), '') Description,
Please help.
Thanks in advance
How to club this two logics together for a single field. Field name is description.
1.) case when right(rtrim(Description),1
2.) replace (replace (Description, char(10), ''), char(13), '') Description,
Please help.
Thanks in advance
ASKER
I checked it doesnot work
ASKER
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 ?
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)
Did it work individually before? Meaning, if you just do this does it remove those characters?
»bp
Replace(Replace(Description,Char(10),''),Char(13),'') Description
»bp
the case probably doesn't work ...
you are missing an "end"
you are missing an "end"
ASKER
yes Bill it works individually.
ASKER
I added end to it.
the else part of the case is optional
but what if the description doesn't contain a ',' ?
but what if the description doesn't contain a ',' ?
ASKER
separately they both are working absolutely fine.
ASKER
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
do you want to replace the cr lf in the other ones or just the ones with ',' ?
that's important if you nest instructions
ASKER
replace char for other ones not with ','
so I am testing for both
so I am testing for both
Yes, END was missing, so should be:
»bp
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
»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 ?
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 ?
ASKER
Okay, will get back to you on this. thanks
ASKER
need to check data more on this
Okay, let us know...
»bp
»bp
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
let me check bill
ASKER
Awesome bill, this is what I was looking for.. thanks so much for you help.
Welcome, glad that finally worked out.
»bp
»bp
Open in new window
»bp