Crystal Reports & SQL Reporting Services Training Consultants

Account Log in:

Username:

Password:

*Don’t seek, don’t search, don’t ask, don’t knock, don’t demand – relax. If you relax, it comes. If you relax, it is there. If you relax, you start vibrating with it* Osho

Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers

Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog

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