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 |
