CODEWITHSUNDEEP
sqlsql-serverstringsql-server-2008-r2
MAK
MAK

Reputation: 7260

Remove first and last dot from string if exists

I want to remove dot from start and end of the string if exists without using CASE and LIKE. 

DECLARE @str1 varchar(max) = 'SQL Server.'
DECLARE @str2 varchar(max) = 'SQL. Server'
DECLARE @str3 varchar(max) = '.SQL Server.'
DECLARE @str4 varchar(max) = '.SQL Server'

Query: 

SELECT CASE WHEN @str1 like '%.' THEN left(@str1, len(@str1) - 1) 
            ELSE @str1 
       END AS String1;

Note: The reason behind not using CASE and LIKE is I am using multiple replace and trim function on @str.

Expected Result:

String1     String2     String3     String4 
--------------------------------------------
SQL Server  SQL. Server SQL Server  SQL Server

Upvotes: 1

Views: 1476

Answers (4)

Rigersong
Rigersong

Reputation: 1

DECLARE @str1 varchar(max) = 'SQL Server.'
DECLARE @str2 varchar(max) = 'SQL. Server'
DECLARE @str3 varchar(max) = '.SQL Server.'
DECLARE @str4 varchar(max) = '.SQL Server'

SELECT CASE WHEN @str1 LIKE '%.' OR @str1 LIKE '.%' THEN REPLACE(@str1, '.', '') ELSE @str1 END String1
    , CASE WHEN @str2 LIKE '%.' OR @str2 LIKE '.%' THEN REPLACE(@str2, '.', '') ELSE @str2 END String2
    , CASE WHEN @str3 LIKE '%.' OR @str3 LIKE '.%' THEN REPLACE(@str3, '.', '') ELSE @str3 END String3
    , CASE WHEN @str4 LIKE '%.' OR @str4 LIKE '.%' THEN REPLACE(@str4, '.', '') ELSE @str4 END String4

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272116

Using SUBSTRING and LENGTH functions:

SELECT str, SUBSTRING(
    str,
    CASE WHEN LEFT(str, 1) = '.' THEN 2 ELSE 1 END,
    DATALENGTH(str) - CASE WHEN LEFT(str, 1) = '.' THEN 1 ELSE 0 END - CASE WHEN RIGHT(str, 1) = '.' THEN 1 ELSE 0 END
)
FROM (VALUES
    ('SQL Server.'),
    ('SQL. Server'),
    ('.SQL Server.'),
    ('.SQL Server')
) AS tests(str)

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44326

This will remove the first and last dot:

DECLARE @t table(strx varchar(max))
INSERT @t values('SQL Server.'),('SQL. Server'),('.SQL Server.'),('.SQL Server')

SELECT 
  STUFF(
    SUBSTRING(strx, 1, LEN(strx) -CASE WHEN strx LIKE '%.' THEN 1 ELSE 0 END),
    1, CASE WHEN strx like '.%' THEN 1 ELSE 0 END, ''
  )
FROM @t

Upvotes: 1

Thom A
Thom A

Reputation: 95557

Perhaps...

SELECT @str = CASE RIGHT(V.str,1) WHEN '.' THEN STUFF(V.str,LEN(v.str),1,'') ELSE V.str END
FROM (VALUES(CASE LEFT(@str,1) WHEN '.' THEN STUFF(@str,1,1,'') ELSE @str END)) V(str);

Upvotes: 1

Related Questions