How do i remove the dot value when there is a null value in sql

Question

How do i remove the dot value when there is a null value in sql

Naresh y 146

HI i have a query where i need the remove the dot value from the first part

in the screen shoot ,field name is cust_value, and it is concatenate with the two files

field 1+ field 2

where the second field is empty i need to remove the "." value (dot value) and other values keep the dot as a separator

Yitzhak Khabinsky 26,586 Reputation points

2022-06-09T15:53:20.233+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

Accepted answer

3 additional answers

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2022-06-09T15:53:20.233+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

Answer 1

Naomi Nosonovsky 8,431

select field1 + case when NULLIF(field2,'') IS NOT NULL then '.' + field2 else '' end as result from dbo.myTable

Answer 2

Erland Sommarskog 122K MVP Volunteer Moderator

Another solution that requires SQL 2017 or later:

   concat_ws('.', field1, nullif(field2, ''))

The first parameter to concat_ws is a separator, the remaining are strings to be concatenated, separated by the separator. A NULL value will result in nothing, that is not even a separator.

Answer 3

Hi @Naresh y ,

Here is a solution that will work starting from SQL Server 2017 onwards.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, cust_value VARCHAR(30));  
INSERT @tbl (cust_value) VALUES  
('terry-01.'),  
('terry-02.'),  
('terry-01.IPL03'),  
('terry-02.IPL03');  
-- DDL and sample data population, end  
  
SELECT *   
	, Result = TRIM('.' FROM cust_value)  
FROM @tbl;

Output

+----+----------------+----------------+  
| ID |   cust_value   |     Result     |  
+----+----------------+----------------+  
|  1 | terry-01.      | terry-01       |  
|  2 | terry-02.      | terry-02       |  
|  3 | terry-01.IPL03 | terry-01.IPL03 |  
|  4 | terry-02.IPL03 | terry-02.IPL03 |  
+----+----------------+----------------+

Answer 4

Jingyang Li 5,896 Volunteer Moderator

create table test (cust_value varchar(20) )  
  
insert into test values('Terry01.' )  
, ('Terry01.' )  
, ('Terry01.IPL_03' )  
, ('Terry01.IPL_01' )  
, ('Terry03.' )  
, ('Terry04.' )  
   
select   
Case when Right(cust_value,1)='.' then reverse(stuff(reverse(cust_value),1,1,'')) else cust_value end cust_value  
from test  
   
  
drop table test

Jingyang Li 5,896 Reputation points Volunteer Moderator

2022-06-09T20:21:03.493+00:00

You can use concat function:
select concat(field1 , '.' + field2) as result
from dbo.myTable

Share via

How do i remove the dot value when there is a null value in sql

3 additional answers

Your answer