Data manipulation using SQL Server Substring so something else

Question

I have a table A like this:

ID   Col1
----------------------
1    xyz-abcccc
2    xyz-jkasdasd
3    abcds-asks
4    asdasdasda-as

I want to get output like this:

    ID   Col1
    -------------
    1    abcccc
    2    jkasdasd
    3    asks
    4    as

I want get output where anything before the dash - is ignored.

Thanks

John Cappelletti · Accepted Answer · 2018-06-25 00:56:42Z

2

charindex() would be a good place to start. The only trick is add a dash within the charindex function as a fail-safe thus avoids throwing an error.

Example

Select ID
      ,Col1 = substring(Col1,charindex('-',col1+'-')+1,len(Col1))
 from YourTable

Returns

ID  Col1
1   abcccc
2   jkasdasd
3   asks
4   as

answered Jun 25, 2018 at 0:56

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Duffer Over a year ago

Thanks, it worked. What should I do if I have more than 2 " - "? –

John Cappelletti Over a year ago

@Duffer Take a peek at stackoverflow.com/questions/43789578/…

Ullas · Accepted Answer · 2018-06-25 04:21:50Z

0

You can use a combination of RIGHT and CHARINDEX functions as well.

Query

select [ID],
case when [Col1] like '%-%' then right([Col1], charindex('-', reverse([Col1]), 1) - 1)
else [Col1] end as [new_col1]
from [your_table_name];

answered Jun 25, 2018 at 4:21

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

Collectives™ on Stack Overflow

Data manipulation using SQL Server Substring so something else

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related