How to write a conditional substring in sql

Question: I have a SELECT statement returning a recordset . Before presenting the recordset , I want to strip out some of the substrings within certain records.

This is an example of the recordset

Brazil

Brazil.com

Argentina.net

Bolivia

The requirement is to strip out any part of the string from the dot and after. For example, “brazil.com” will become “brazil”.

The problem I have currently is by running the SUBSTRING function across all the rows, if there is no dot in the row , the rown will return as blank. The only rows returning are the ones with a dot!

How can I write the sql statement with the SUBSTRING function is ONLY executed on condition of the dot appearing?

Answer: When you use a SUBSTRING function there is no built-in conditional functionality. But you can achieve a conditional SUBSTRING execution a few different ways. The method I most commonly use is the SELECT CASE functionality.

The logic is to first – check for the existence of a dot , if the dot exists than apply the SUBSTRING functionality on the row , if the dot does not exist than  return the row as normal

Here is an example code snippet – outlining the process. The WHEN part of the CASE statement uses sql CHARINDEX to check if the position of dot is greater than 0.

SELECT case 
WHEN charindex('.',country,1) > 0 then SUBSTRING(country,1,charindex('.',country)-1)
ELSE country
END
FROM my_countries

For best practise – I prefer to pass the full recordset to the client and process the substring and charindex at the application side. This would depend on the circumstances.

Read More

How to use Powershell string functions