CODEWITHSUNDEEP
sqlt-sql
Looking_for_answers
Looking_for_answers

Reputation: 343

I want to remove part of string from a string

Thank you in advance.

I want to remove string after . including ., but length is variable and string can be of any length.

1)Example:
Input:- SCC0204.X and FRK0005.X and RF0023.X and ADF1010.A and HGT9010.V

Output: SCC0204 and FRK0005 and RF0023 and ADF1010.A and HGT9010.V

I tried using the charindex but as the length keeps on changing i wasn't able to do it. I want to trim the values with ending with only X

Any help will be greatly appreciated.

Upvotes: 3

Views: 17362

Answers (7)

Alan Burstein
Alan Burstein

Reputation: 7918

Update: I just missed one of the comments where the OP clarifies the requirement. What I put together below is how you would deal with a requirement to remove everything after the first dot on strings ending with X. I leave this here for reference. 

;WITH cte_TestData(Code) AS
(
  SELECT 'SCC0204.X'  UNION ALL -- ends with '.X'
  SELECT 'FRK.000.X'  UNION ALL -- ends with '.X', contains multiple dots
  SELECT 'RF0023.AX'  UNION ALL -- ends with '.AX'
  SELECT 'ADF1010.A'  UNION ALL -- ends with '.A'
  SELECT 'HGT9010.V'  UNION ALL -- ends with '.V'
  SELECT 'SCC0204.XF' UNION ALL -- ends with '.XF'
  SELECT 'FRK0005'    UNION ALL -- totally clean
  SELECT 'ABCX'                 -- ends with 'X', not dots
)
SELECT 
  orig_string = code,
  newstring   = 
  SUBSTRING
  (
    code, 1,
    CASE 
      WHEN code LIKE '%X'
      THEN ISNULL(NULLIF(CHARINDEX('.',code)-1, -1), LEN(code))
      ELSE LEN(code)
    END
  )
FROM cte_TestData;

FYI - SQL Server 2012+ you could simplify this code like this:

SELECT 
  orig_string = code,
  newstring   = 
  SUBSTRING(code, 1,IIF(code LIKE '%X', ISNULL(NULLIF(CHARINDEX('.',code)-1, -1), LEN(code)), LEN(code)))
FROM cte_TestData;

Upvotes: 1

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

Hope this helps. The code only trims the string when the value has a decimal "." in it and if that value is equal to .X

;WITH cte_TestData(Code) AS
(
SELECT 'SCC0204.X' UNION ALL
SELECT 'FRK0005.X' UNION ALL
SELECT 'RF0023.X' UNION ALL
SELECT 'ADF1010.A' UNION ALL
SELECT 'HGT9010.V' UNION ALL
SELECT 'SCC0204' UNION ALL
SELECT 'FRK0005'
)
SELECT CASE 
        WHEN CHARINDEX('.', Code) > 0 AND RIGHT(Code,2) = '.X'
            THEN SUBSTRING(Code, 1, CHARINDEX('.', Code) - 1)
        ELSE Code
        END
FROM cte_TestData

If the criteria is only to replace remove .X then probably this should also work

;WITH cte_TestData(Code) AS
(
SELECT 'SCC0204.X' UNION ALL
SELECT 'FRK0005.X' UNION ALL
SELECT 'RF0023.X' UNION ALL
SELECT 'ADF1010.A' UNION ALL
SELECT 'HGT9010.V' UNION ALL
SELECT 'SCC0204' UNION ALL
SELECT 'FRK0005'
)
SELECT REPLACE (Code,'.X','')
FROM cte_TestData

Upvotes: 3

Praveen ND
Praveen ND

Reputation: 560

Please check the below code. I think this will help you.

DECLARE @String VARCHAR(100) = 'SCC0204.X'
IF (SELECT RIGHT(@String,2)) ='.X'
   SELECT LEFT(@String,CHARINDEX('.', @String) - 1)
ELSE 
  SELECT @String

Upvotes: 1

Naim Halai
Naim Halai

Reputation: 375

With SUBSTRING you can achieve your requirements by below code.

SELECT SUBSTRING(column_name, 0, CHARINDEX('.', column_name)) AS col
FROM your_table

If you want to remove fixed .X from string you can also use REPLACE function.

SELECT REPLACE(column_name, '.X', '') AS col

Upvotes: 0

Yoav24
Yoav24

Reputation: 363

I think your best bet here is to create a function that parses the string and uses regex. I hope this old post helps:

Perform regex (replace) in an SQL query

However, if the value you need to trim is constantly ".X", then you should use select replace(string, '.x', '')

Upvotes: 1

Mansoor
Mansoor

Reputation: 4192

Use LEFT String function :

 DECLARE @String VARCHAR(100) = 'SCC0204.XXXXX'
 SELECT LEFT(@String,CHARINDEX('.', @String) - 1)

Upvotes: 1

beejm
beejm

Reputation: 2481

Assuming there is only one dot

UPDATE TABLE 
SET column_name = left(column_name, charindex('.', column_name) - 1)

For SELECT

select left(column_name, charindex('.', column_name) - 1) AS col
from your_table

Upvotes: 4

Related Questions