How to split a string after specific character in SQL Server

Question

I am working in SQL Server studio manager 2014

I found this and it is sort of close to what I am wanting, but not quite perfect.

Col2 = RIGHT([HospCode],LEN([HospCode])-CHARINDEX('.',[HospCode]))

My data looks like this

ABC.FRED.123
ABC.STEVE
ABC.SUSAN
ABC.TED.4456
ABC.WILL
ABC.TABITHA.11
ABC.TODD

I want to get rid of the "ABC." and the ".xxx" number section IF it exists and only see the Name section. So I want my final output to be something like:

FRED
STEVE
SUSAN
TED
WILL
TABITHA
TODD

Can anyone assist? Thank you in advance!

John Cappelletti · Accepted Answer · 2017-04-12 21:47:36Z

2

One simple approach is to use ParseName() with a Reverse() (or two)

Since Name will always be the second of two or three, we reverse the string, apply ParseName(...,2), and Reverse the result back.

Declare @YourTable table (HospCode varchar(50))
Insert Into @YourTable values
('ABC.FRED.123'),
('ABC.STEVE'),
('ABC.SUSAN'),
('ABC.TED.4456'),
('ABC.WILL'),
('ABC.TABITHA.11'),
('ABC.TODD')

Select A.*
      ,Reverse(ParseName(Reverse(HospCode),2))
 From @YourTable A

Returns

HospCode        (No column name)
ABC.FRED.123    FRED
ABC.STEVE       STEVE
ABC.SUSAN       SUSAN
ABC.TED.4456    TED
ABC.WILL        WILL
ABC.TABITHA.11  TABITHA
ABC.TODD        TODD

edited Apr 12, 2017 at 21:47

answered Apr 12, 2017 at 21:26

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

2 Comments

DoNotLikeIT Over a year ago

John, You rock my man!!! NI have spent 4 hours messing with this today! I have no idea whats happening here so I will read up on the Parse function. Seriously THANK YOU

John Cappelletti Over a year ago

@DoNotLikeIT Happy it helped. I'll add some more context

Collectives™ on Stack Overflow

How to split a string after specific character in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related