Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Don't make the same mistake twice
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

13
January

How do I extract characters before a specific character in a string field using SQL

The problem:

The client had a field that contains company names, but unfortunately when the data was entered, it was not consistent.

Some had just the company name, while others had the company name and account number separated by a dash (-).

They needed a way to extract just the company name part of the data.

Because of this in consistency, using the CharIndex function may result in an error message



Solution:

Use the ISNULL and NULLIF functions to check for the error, (Charindex =0) before the rest of the code is run:

DECLARE @CompanyName VARCHAR(50)
 SET @CompanyName= 'Testing My Code Ltd'
 
SELECT LEFT(@CompanyName, ISNULL(NULLIF(CHARINDEX('-', @CompanyName) - 1, -1), LEN(@CompanyName))) AS [Company Name]

Since there's no dash in the company name, the whole company name is returned as the Company Name field.


A function was then created to extract only the company name from company name field of the database.

CREATE FUNCTION [dbo].[ufn_CompanyName]

( @fCompanyName VARCHAR(50)) RETURNS VARCHAR(50) AS BEGIN

RETURN LEFT(@fCompanyName, ISNULL(NULLIF(CHARINDEX('-', @fCompanyName) - 1, -1), LEN(@fCompanyName))) END


If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us

MAKE A COMMENT

Name *

Email Address *

Comment *