-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
  • 69,699
  • 7
  • 57
  • 89
1

Look at this one

Select LTRIM(SUBSTRING(col, CHARINDEX('/', col) + 1, LEN(col)))
from table
Ullas
  • 11,201
  • 4
  • 31
  • 47
shankarsh15
  • 1,897
  • 1
  • 10
  • 15
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,201
  • 4
  • 31
  • 47