xxxsenatorxxx xxxsenatorxxx - 2 months ago 8
SQL Question

Compare and update two characters in a string

I have a table with name

table1
containing:

col1
a.b

1.1
2.2
3.3
1.3
2.3


enter image description here

I want compare left character of dot (a) with right character of dot (b), and if a = b, remove
.b
.

So
table1
must be changed to this:

col1
a.b

1
2
3
1.3
2.3


enter image description here

Answer

Try this:

DECLARE @tbl1 as TABLE(
   Id  INT,
   col1  VARCHAR(20)
)

INSERT INTO @tbl1 VALUES(1,'1.1')
INSERT INTO @tbl1 VALUES(2,'2.2')
INSERT INTO @tbl1 VALUES(3,'3.3')
INSERT INTO @tbl1 VALUES(4,'1.3')
INSERT INTO @tbl1 VALUES(5,'1.4')

SELECT
    Id,
    CASE WHEN SUBSTRING(col1,0,CHARINDEX('.',col1))=SUBSTRING(col1,CHARINDEX('.',col1)+1,len(col1))
    THEN SUBSTRING(col1,0,CHARINDEX('.',col1))
    ELSE col1
    END
FROM
@tbl1

UPDATE:

UPDATE @tbl1 
    SET col1=SUBSTRING(col1,0,CHARINDEX('.',col1))
WHERE SUBSTRING(col1,0,CHARINDEX('.',col1))=SUBSTRING(col1,CHARINDEX('.',col1)+1,len(col1))
Comments