CODEWITHSUNDEEP
sqlsql-server
Musi
Musi

Reputation: 195

How to remove everything before a certain character in SQL Server?

I have entered data into my SQL server. I want to remove all the characters before a hyphen. There are different amounts of characters before the "-".

For Example:

ABC-123
AB-424
ABCD-53214

I want this result:

123
424
53214

I am new to SQL Server and really need help.
Thank You in advance.

Upvotes: 16

Views: 90602

Answers (3)

Thom A
Thom A

Reputation: 95914

Personally, I prefer to use STUFF to do this, rather than RIGHT. Then you don't need to get the length of value. You'll still need CHARINDEX to get the position of the character though:

STUFF(YourColumn, 1, CHARINDEX('-',YourColumn),'')

So this takes the value of YourColumn, and then replaces the characters between position 1 and the position of the hyphen (-) character in YourColumn, with a zero length string ('').

If you don't want a string returned if the character isn't in the string, you can add a NULLIF, to change to 0 from the CHARINDEX to NULL.

STUFF(YourColumn, 1, NULLIF(CHARINDEX('-',YourColumn),0),'')

Working Example:

SELECT V.YourColumn,
       STUFF(YourColumn, 1, CHARINDEX('-',YourColumn),'') AS RemoveUpToDelimiter,
       STUFF(YourColumn, 1, NULLIF(CHARINDEX('-',YourColumn),0),'')  AS RemoveUpToDelimiter_NULLifNone
FROM (VALUES('ABC-123'),
            ('AB-424'),
            ('ABCD-53214'),
            ('789'))V(YourColumn);

Upvotes: 0

mohan111
mohan111

Reputation: 8865

may be the other way you can do it by using reverse and Char Index 

DECLARE  @Table1 TABLE 
    (val varchar(10))
;

INSERT INTO @Table1
    (val)
VALUES
('ABC-123'),
    ('AB- 424'),
    ('ABCD-53214')
select reverse(substring(reverse(val),0,CHARINDEX('-',reverse(val)))) from @Table1

Upvotes: 1

APH
APH

Reputation: 4154

Try this:

right(MyColumn, len(MyColumn) - charindex('-', MyColumn))

Charindex finds the location of the hyphen, len finds the length of the whole string, and right returns the specified number of characters from the right of the string.

Upvotes: 41

Related Questions