Clever Geek Handbook

I want to remove part of a string from a string

Thanks in advance.

I want to delete the line after. including., but the length of the variable and the string can be 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 charindex, but as the length keeps changing I was unable to do so. I want to truncate values, ending with only X

Any help would be greatly appreciated.

+3


source to share


7 replies


Assuming there is only one point

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
+2


source


Use the LEFT String function:



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


source


I find it best to create a function that parses the string and uses a regular expression. Hope this old post helps:

Execute regex (replace) in SQL query

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

+1


source


With SUBSTRING, you can fulfill your requirements with the code below.

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

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

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


source


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
+1


source


Hope this helps. The code only truncates the string when the value has a decimal value "." in it, and if this value is.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 only replace remove is the criterion .X

, then it probably should work as well

;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
+1


source


Update: I just missed one of the comments where the OP clarifies this requirement. Below I describe how you will deal with the requirement to delete everything after the first dot on lines ending in 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 can 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;
+1


source






More articles:


All Articles