CODEWITHSUNDEEP
sql-serversql-server-2008-r2
MAK
MAK

Reputation: 7260

SQL Server 2008 R2: Update with condition

Table: Dots

CREATE TABLE dots
(
    cola VARCHAR(50),
    NAME VARCHAR(10),
);

Insertion:

INSERT INTO dots VALUES('59.96.12.563','');
INSERT INTO dots VALUES('594.966.127.5683','');
INSERT INTO dots VALUES('5998.8896.4412.22563','');
INSERT INTO dots VALUES('791','');
INSERT INTO dots VALUES('891','');
INSERT INTO dots VALUES('691','');

Note: Now I want to update the NAME column in the table Dots to the value IP for those records who contains the 3 dots on it in column Cola. For example as shown below in the expected result.

Result: 

Cola                    NAME
--------------------------------                    
59.96.12.563            IP
594.966.127.5683        IP
5998.8896.4412.22563    IP
791
891
691

Upvotes: 2

Views: 52

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

The correct way of checking would be using LIKE, this will check for exactly 3 dots:

WHERE
  cola like '%.%.%.%'
  and cola not like '%.%.%.%.%'

If you fancy the REPLACE method. This is the correct syntax:

 WHERE 
   LEN(REPLACE(cola, '.', '.X')) - LEN(cola) = 3

The reason is that

LEN(cola) - LEN(REPLACE(cola, '.', '')) = 3

Will fail in case the text ends with space period.

Example:

SELECT LEN(REPLACE('.  .', '.', ''))

Returns 0 indication there are 4 dots in the text.

This may not be an issue since the spaces are probably not existing in your IP addresses.

Upvotes: 1

Abhishek
Abhishek

Reputation: 2490

Try the below code snippet - 

UPDATE dots SET Name = 'IP' 
WHERE (LEN(cola) - LEN(REPLACE(cola, '.', ''))) / LEN('.')  = 3

Upvotes: 4

Code Different
Code Different

Reputation: 93141

Try this:

UPDATE dots
  SET   NAME = 'IP'
  WHERE cola LIKE '%.%.%.%'

Upvotes: 1

Related Questions