-1

my table have column contain data like this.

SQL 2008

97W
125/ 122Q
121/ 118Q
121/ 118S
123/ 120S
112H
111H

i am trying to remove data before / so output will look like as

97W
122Q
118Q
118S
120S
112H
111H

can anyone share experience how can i achieve if came across such scenario.

Thanks,

isle chow
  • 83
  • 5
  • check answer - http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server – Pragnesh Khalas May 10 '16 at 05:52
  • If the output is going to your presentation layer eventually, you could handle this from your app. SQL Server isn't the greatest tool for regular expressions. – Tim Biegeleisen May 10 '16 at 05:52

3 Answers3

2

Try this:

SELECT LTRIM(RIGHT(mycol, LEN(mycol) - CHARINDEX('/', mycol)))
FROM mytable

Demo here

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
1

Look at this one

Select LTRIM(SUBSTRING(col, CHARINDEX('/', col) + 1, LEN(col)))
from table
Ullas
  • 11,450
  • 4
  • 33
  • 50
shankarsh15
  • 1,947
  • 1
  • 11
  • 16
0

Use a CASE expression to check whether the string contains /, if yes use CHARINDEX to find the index of / and get the right part. Else the string as it is.

Query

SELECT 
    CASE WHEN your_column_name LIKE '%/%' 
    THEN LTRIM(RIGHT(your_column_name, CHARINDEX('/', REVERSE(your_column_name), 1) - 1)) 
    ELSE your_column_name END AS new_string
FROM your_table_name;

SQL Fiddle Demo

Ullas
  • 11,450
  • 4
  • 33
  • 50