Scribd
Upload
635 views

DS Function

The document contains examples of SQL functions and expressions for string manipulation and pattern matching that can be used to extract, replace, or transform parts of strings. This includes using functions like REPLACE_SUBSTR_EXT, MATCH_REGEX, and RIGHT to remove characters or substrings, extract portions of strings, and check for patterns. It also provides examples of using IFTHENELSE and DECODE to conditionally transform values based on string patterns or conditions.

Uploaded by

nikkusonik
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
635 views

DS Function

The document contains examples of SQL functions and expressions for string manipulation and pattern matching that can be used to extract, replace, or transform parts of strings. This includes using functions like REPLACE_SUBSTR_EXT, MATCH_REGEX, and RIGHT to remove characters or substrings, extract portions of strings, and check for patterns. It also provides examples of using IFTHENELSE and DECODE to conditionally transform values based on string patterns or conditions.

Uploaded by

nikkusonik
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

date(component("DATE", 1), component("DATE", 2), component("DATE", 3))

leftstr(string(<date_field>), 4)
TO_CHAR(sysdate, 'DD-MON-YYYY HH:MI:SS AM') NOW, TO_CHAR(sysdate+1/
(24*60*60),'DD-MON-YYYY HH:MI:SS AM') NOW_PLUS_1_SEC from dual;

replace_substr_ext('ayyya<newline>yyya<ne wline>yyyayyy', 'a/x000a', 'B', 2,


2)
Results in 'ayyyByyyByyyayyy'

replace_substr(ColumnName,chr('10'),'')

remove them using REPLACE_SUBSTR_EXT if you are going to search for only a
specific set of special char.
remove all the special characters in a string .How to do this?
For example @34!^78a5 should be changed to 3478a5.

NEW LINE - replace_substr_ext(QRY.COL_NAME,'/n','',NULL,NULL)


FORM FEED - replace_substr_ext(QRY.COL_NAME,'/f','',NULL,NULL)
HORIZONTAL TAB - replace_substr_ext(QRY.COL_NAME,'/t','',NULL,NULL)

match_regex(Query."EMPLOYEE NAME",literal('[a-zA-Z0-9]'),NULL)=1

In this example the data was CKI=ICD9!250.02 and the user required all the
values after the ! returned, i.e. 250.02. also went on to state that the number of
characters after the 250.02 may vary in length.
=(Length([Object])-Pos([Object];!))
=Right([Object];(Length([Object])-Pos([Object];!)))

need to remove all of the special characters on a standard keyboard leaving just
the alpha-numeric ones (A to Z and 0 to 9).

replace_substr_ext(replace_substr_ext(replace_substr_ext(Query_2.MFRPN,'+','',1
,20)
,'_','',1,20),'-','',1,20) ) )

The following statement will replace the dash with nothing (which is good);
replace_substr_ext(Query_2.MFRPN,'-','',1,20)
Remove -FX from string like: LR78S2N-FX
replace_substr(replace_substr(POS_VP_INPUT."Key 1 (Product)", '-FX',''),'',' ')
Q: MLR78S2N-FX(
Remove M from staring wherever and FX also (
Result: LR78S2N
Solution:
#decode(
#ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1 (Product)")) LIKE 'M
%',substr(ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1
(Product)")),2,length(ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1
(Product)")))),
#POS_VP_INPUT."Key 1 (Product)" LIKE '%%',word_ext( ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1 (Product)")),1,'-'),
#POS_VP_INPUT."Key 1 (Product)" LIKE '%
(%',word_ext( ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1 (Product)")),1,'('),
#ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1 (Product)")))

#decode(
#ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1 (Product)")) LIKE 'M
%',substr(ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1
(Product)")),2,length(ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1
(Product)")))),
#POS_VP_INPUT."Key 1 (Product)" LIKE '%%',word_ext( ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1 (Product)")),1,'-'),
#POS_VP_INPUT."Key 1 (Product)" LIKE '%
(%',word_ext( ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1 (Product)")),1,'('),
#ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1 (Product)")))

#decode(
#ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1 (Product)")) LIKE 'M
%','substr(ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1
(Product)")),2,length(ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1
(Product)")))),
#POS_VP_INPUT."Key 1 (Product)" LIKE '%%',word_ext( ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1 (Product)")),1,'-'),
#POS_VP_INPUT."Key 1 (Product)" LIKE '%
(%',word_ext( ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1 (Product)")),1,'('),
#ltrim_blanks( rtrim_blanks( POS_VP_INPUT."Key 1 (Product)")))

Example: diff way to use of ifthenelse and decode


Let me give you an example of it
Source
Query
Target
Name Name Set Name2 = 'EXT' Where Name1 = Name
1
2
'SAM'
1
SAM POL
SAM
XXX YUN
XXX

Name
2
EXT
YUN

Name1= 'SAM' set Name2='EXT'


Solution:
ifthenelse(Name1='SAM','EXT',Name2) then,
Decode( Material type ='XYZ' , 'YES', Default Value)
How to use PostLoad Command ?
Double click the target table and it will open up a series of tabs. One of the tab is
post load command where you can enter the SQL query (update command) and
save it. Run the job after that and you will get the result.
Problem: Rule for Default name: Break at the last space prior to the 35th
character (i.e. if the length is more than 35 char, we want only complete
sentence/word)

For example if default name : Indian institute of management Bangalore

which is more than 35 char, So first 35 letter will be ~ Indian institute of


management Bang, But we want only complete words as name Indian institute
of management . we dont want word Bang at last.

Overflow of the name will be managed in Name2 so no issue with trimJ


I have put temp fix to this requirement and its working fine. Please suggest if you
have any other logic to implement same in SAP BODS
Solution:
decode(length(Default_Name) > 35, rtrim( substr(Default_Name, 1, 35),
'()qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM1234567890-,.
&*":;<>?/#@' ), Default_Name)

tried this with a custom function and it did work perfect.

Custom Function:(CF_StringRetrieve)
# Assign the input string to a local variable
$L_Stmnt = $P_InString ;
#Initialize loop counter
$L_LoopCounter = 35;

#Reverse the statement


while ( $L_LoopCounter > 0 )
begin
$L_StrRev = $L_StrRev || Substr($L_Stmnt, $L_LoopCounter, 1);
$L_LoopCounter = $L_LoopCounter - 1;
end

#Extracting the substring of the statement after first space, so that the
extra truncated part is removed
$L_StrRev = substr($L_StrRev, index( $L_StrRev, ' ', 1 ) , length( $L_StrRev
));

#Reassigning the initial value of local string


$L_Stmnt = '';

$L_LoopCounter = length( $L_StrRev );


while ( $L_LoopCounter > 0 )
begin
$L_Stmnt = $L_Stmnt || Substr($L_StrRev, $L_LoopCounter, 1);
$L_LoopCounter = $L_LoopCounter - 1;
end

Return $L_Stmnt ;

Mapping to the output field:


ifthenelse(length( Field1) < 35, Field1, ifthenelse(index( Field1, ' ',1) IS
NULL, substr( Field1,1,35), rtrim_blanks(CF_StringRetrieve( Field1))))

Q:find the value of ' % ASSOC%' in the name and replace it to 'Assoc.' using ifthenelse.
SOL:
ifthenelse
(
Table.field_name like '%ASSOC%',replace_substr( Table.field_name,'ASSOC','Assoc.')
,Table.field_name
)

Sol:
decode(Table.field_name like '%ASSOC%, 'Assoc.' , ,Table.field_name)
decode(Table.field_name like '%ASSOC%, 'Assoc.' , ,Fail)

You might also like