How to write a conditional substring in sql

30 March,2017 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to write a conditional substring in sql


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer