SQL output using XML functions

I am not sure how XML function works in Oracle but it indeed gives ouptput in a much formatted way which we need most of the times.

Consider a table with following records
col1 col2
1 A
2 B
3 C
1 D
2 S

Execute this query
select col1,
rtrim (xmlagg (xmlelement (e, col2 ',')).extract ('//text()'), ',') col2 from tbl1
group by col1
the output will be
col1 col2
1 A,D
2 B,S
3 C

Most of the times we need similar output as shown above which we will do by writing long PL/SQL codes by there is a much easier way to achieve this by using Oracle XML functions. Explore your ideas in XML / Oracle functions

Using Escape character in Oracle

In Oracle the '_' character is used to match exactly one character, wherease '%' is used to match zero or more occurrences of any characters. For example if we need to search a string in Oracle with '_' prsence in the column, then in such cases normal LIKE operator will not work. We need to escape the character '_'.

Here is the syntax for escaping the character in Oracle

SELECT name FROM emp WHERE id LIKE '%/_%' ESCAPE '/';

Oracle: Difference between Rank and Dense_Rank

RANK leaves gaps while ranking the records whereas DENSE_RANK doesn't leave any gaps. For eg, if we have a tie at the 4th position and there are 2 records with the same value then

RANK - will place both the records in the 4th position, and will have the next record at the 6th position (leaving a gap of 1 position)

DENSE_RANK - will also place both the records in the 4th position, but the next record will be placed at 5th position (no gap will be placed).