I have application where in dropdown I want both Capital and Small Letter values.
for example. I have entity with a column name Customer Designation. I want to fetch all the designation available in my Entity.
I tried following method
1. Aggregate Group By Customer.Designation
2. Using SQL Tool grouping
My requirement is as follows
Can I ignore case in a group by? For example if there is a table of states but it has records with "Alabama" and "alabama", or "Alaska" and "alaska" and I want the group by that column and get back a both.
ex, I have customer designation 'Account Manager - Health' and 'ACCOUNT MANAGER - HEALTH'.
and I want both to be fetched in group by.
The group by on an aggregate is case insensitive, so maybe the attributes are not the same.
'Account Manager - Health' has TWO spaces before "-"
'ACCOUNT MANAGER - HEALTH' has ONE space before "-"
So even if the case would be the same, the string value is different because of that extra space.
Hi @Chandrashekhar Mankar
there are two options that use can choose from
SELECT <columns.....>
FROM <your_table>
WHERE BINARY <column_name> = <your_value>;
SELECT <columns...>
WHERE NLSSORT(<column_name>, 'NLS_SORT=BINARY_AI') = NLSSORT(<your_value>, 'NLS_SORT=BINARY_AI');
Hope that this helps.
Regards
hi is this what you want to achieve?
left (result of the query)
right (database record)
you can do Collate SQL_Latin1_General_CP1_CS_AS in adv SQL via group by or distinct
Recently the same question came up, and I provided a solution here. It's basically what Sherwin suggested above, use a COLLATE with the appropriate collation string.
@Chandrashekhar Mankar you can also use the advance SQL widget with similar query as below
select Initcap(column_name) from Table_Name group by Initcap(column_name)