CODEWITHSUNDEEP
sql-server
user3922915
user3922915

Reputation: 7

Substring Statement

I am using a SQL substring statement to return periods after a Middle Name initial, such as, "H." When there is no Middle name to return, the period still returns in a field that should be null.

Here is my statement: 

substring([Middle Name],1,1)+('.')

Upvotes: 1

Views: 78

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172398

Try this:

substring(nullif([Middle Name], ''), 1, 1) + '.'

You may check the NULLIF

Returns a null value if the two specified expressions are equal.

Upvotes: 2

Alex K.
Alex K.

Reputation: 175758

As NULL + anything == NULL how about:

select left(nullif([Middle Name], ''), 1) + '.'

Or

select case when [Middle Name] is not null then left([Middle Name], 1) + '.' end

Upvotes: 3

Related Questions