SQL Replace Function and NULLS

Question

This should be simple but I can figure it out for the life of me. Basically I have a stored procedure that updates a database. In the database I have forename and surname columns and the stored procedure is doing the business logic. What I want to be able to do is if someone tries to enter multiple names it identifies that based on a series of replaces statements and returns NULL if it finds any, for example like in the code below it is looking for a "/" (as if it was looking for someone doing something like John / Jill) just like in the sample code below:

Declare @Forename Varchar(50)

Set @Forename = 'John'

Select Replace(@Forename,'/',NULL)

However instead of retuning 'John' it returns NULL even though there is no / present. I've had a google about but I can't find anything. Of course in my real code there are many nested replace statements but this is just to show you what the issue is i am up against. Any help would be great.

Thanks in advance

Gordon Linoff · Accepted Answer · 2017-02-16 14:30:17Z

2

NULL in almost any operation returns NULL, so the result is not surprising. Probably the simplest way to do what you want in SQL Server is:

Select left(@Forename, charindex('/', @Forename + '/') - 1)

The charindex() finds the / if any -- the + '/' ensures that it finds a slash. The left() then chooses all characters up to the one before.

answered Feb 16, 2017 at 14:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SQL_Kid Over a year ago

That's a great solution, but what if I had multiple nests? like: replace(replace(replace(@Forename,'/',NULL),' or ',NULL),' and ',NULL)

dean · Accepted Answer · 2017-02-16 14:57:02Z

1

As per documentation:

Returns NULL if any one of the arguments is NULL.

https://msdn.microsoft.com/en-us/library/ms186862.aspx

EDIT:

If I understand your original requirement correctly, you need something like this:

select case when @forename like '%[/]%' then null else @forename end

Here you can extend the '%[/]%' with additional "separator" characters.

edited Feb 16, 2017 at 14:57

answered Feb 16, 2017 at 14:35

dean

10.1k2 gold badges27 silver badges27 bronze badges

2 Comments

SQL_Kid Over a year ago

There we go, I didn't fully understand that. I should probably pay more attention to BOL. Thanks for pointing it out for me

SQL_Kid Over a year ago

A case statement should do the trick, thank you sir. Accepted as Answer

Prasad Shetty · Accepted Answer · 2017-02-17 04:09:46Z

0

You have to create custom Split method in SQL, which will split your name string by '/' delimiter. If your @Forename string has '/' in it.

Below is the sample code for Split method :

CREATE FUNCTION [dbo].[SplitString] 
( 
    @string NVARCHAR(MAX),
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END
GO

Usage :

Select * from dbo.SplitString(@Forename,'/')

answered Feb 17, 2017 at 4:09

Prasad Shetty

1211 silver badge5 bronze badges

1 Comment

SQL_Kid Over a year ago

Thanks for the answer, but I think there are much easier and less resource intensive ways around to achieve validation checks/ especially as this is just for 1 special character when In reality there are several. But again, thanks for the imput

Collectives™ on Stack Overflow

SQL Replace Function and NULLS

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related