Product:
Microsoft SQL azure
Issue:
In a view, we want to exclude all text in column “konto” after character ” – ”
Solution:
In the select statement use something like this:
IIF (SUBSTRING ([Konto],1, CHARINDEX (' - ',[Konto])) ='', [Konto] ,SUBSTRING ([Konto],1, CHARINDEX (' - ',[Konto]))) as [Konto]
Substring extract text in column konto from first position to where we find the – character, this position is given with charindex, and then we check if the result is empty. In case the string does not contain a -, then we get a empty value. Then we show the orginal value – otherwise we show the substring without all text after – in the field.
SUBSTRING(string, start, length)
CHARINDEX(substring, string, start)
IIF(condition, value_if_true, value_if_false)
More Information:
SUBSTRING, PATINDEX and CHARINDEX string functions in SQL queries
https://www.w3schools.com/sql/sql_isnull.asp
https://www.geeksforgeeks.org/sql/sql-statement-to-remove-part-of-a-string/
https://help.zebrabi.com/kb/power-bi/sort-a-hierarchy-by-custom-sort-logic/