Looking_for_answers Looking_for_answers - 10 days ago 6
SQL Question

I want to remove the value after *.*, but it the value of the length is not definite

Thank you in advance.

I want to remove the values after ., but the length is not defined it keeps changing but it should not take the values after the second FULL STOP.

1)Example:

Input:-

ROL0602.E.DCM.5264403 and COK0105.F.SKE and CLT005.02A.FCM.65721


output:
ROL0602.E and COK0105.F and CLT005.02A


2) example :
Input:
SKE-5700-00211-000


output:
SKE-5700-00211


These are the two columns i want some help with.

I tried using the charindex but as the length keeps on changing i wasn't able to do it.

Answer

SHnugo's solution is excellent but will fail if there is only one dot (.) in the string. Here's a tweaked version that will handle that (note my comments).

DECLARE @tbl TABLE(YourValue VARCHAR(100));
INSERT INTO @tbl VALUES
 ('ROL0602.E.DCM.5264403'),('COK0105.F.SKE'),('CLT005.02A.FCM.65721'),('CLT099.02ACFVVV721'), ('SKE-5700-00211-000');

SELECT 
  CASE 
    --If there are two or more dots(.) then return everything up to the second dot:
    WHEN LEN(YourValue) - LEN(REPLACE(YourValue,'.','')) > 1
      THEN LEFT(YourValue,CHARINDEX('.',YourValue,CHARINDEX('.',YourValue,1)+1)-1)
    ELSE YourValue -- if there are 1 or 0 dots(.) then return the entire value
  END    
FROM @tbl AS tbl;