substrings patindex charindex

Question

hello every one 

          i need help for 1st question and 2nd question below, i need alternate solutions.

its not working for all kind of data. i need help

STARS

	DOB	PhoneNumber	SSN	City
Jolie, Angelina	1942-10-17	(123)4567893	865465489	New York
Pitt, Brad	1978-07-03	9700905557	789-65-6545	Detroit
Freeman^Morgan^M	1978-07-03	5798956985x123	456895256	San Jose
Green II, Eva A	1975-02-20	789512353	891245898	Denver
Spielberg.steven	2013-01-24	4654646542	465465465	Portland
Fox   Megan  J	1955-08-14	3658975469	586-25-7891	Las Vegas
Roberts Julia	1955-08-14	(865)7825869	756-78-4691	Omaha
Roshan Hrithik	989-12-03	9966282726	256-36-8981	Okland

1.    How to check for this pattern (LASTNAME^FIRSTNAME^MIDDLE) and how do I separate the same into LASTNAME, FIRSTNAME, MIDDLEINITIALS? Use necessary string functions.(Use wildcard if applicable, use DRL/DQL) (3m)<o:p></o:p>

2.    How do I separate this format of data (PhonenumberXextension) into phonenumber, extension?Write the syntax using the reference table..(Use wildcard if applicable, use DRL/DQL) (2m)<o:p></o:p>

 1st question solution 

select name,substring(name,1,patindex('%^%',name)-1) [Last Name],right(name,1) [Middlename]

,substring(reverse(name),1,patindex('%^%',reverse(name))-1) [Middlename]

,substring(name,patindex('%^%',name)+1,charindex('^',name)-2) [Firstname]  from stars where name like '%^%'

2nd question solution

select PhoneNumber, replace(replace(PhoneNumber,'(',''),')','') from stars where PhoneNumber like '%(%' or PhoneNumber like '%)%'

update stars set PhoneNumber=replace(replace(PhoneNumber,'(',''),')','') where PhoneNumber like '%(%' or PhoneNumber like '%)%'

or

2nd question solution

select PhoneNumber, substring(phonenumber,1,patindex('%x%',phonenumber)-1) [phone Number]

,substring(phonenumber,patindex('%x%',phonenumber)+1,len(phonenumber)) [Extension] from stars where PhoneNumber like '%x%'

can any one please answer me another solution for 1st question becuase the query what i wrote doesn't work for all kinds of data. i need help.

Thanks

kumar

Answer 1

I see people with spaces between names how do you want to edit them?

create table #T (
  [Name] varchar(40)
)

insert into #T ([Name]) values ('Doe^John^E')
insert into #T ([Name]) values ('Doe,Andy')
insert into #T ([Name])  values ('Doe^Tim^W')
---go

select LastName,FirstName,  MiddleName
from (
  select
    Name,
    substring(Name,1,case when Comma=0 then 1 else Comma end -1) LastName,
MiddleName,
    substring(Name,Comma+1,Spce-Comma-1) FirstName
    from (
    select
      Name,
      charindex('^',Name) Comma,
      right(Name,charindex('^',reverse(Name))-case when charindex('^',Name)=0 then 0 else 1 end)  MiddleName,
len(Name)-1 Spce
     from #T 
  ) D
) SplitNames

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 2

 Thank you URI DIMANT for the solution.

some of the names are separated with commas, some are separated with .  means dot, some are separated with spaces.

my requirement is to split all the NAmes into lastname, firstname and middlename columns separately.

one common thing is all names will in the same order last,first,middle name, but they are separated by , or . or SPACE. 

CAN we use reverse function or len function here.

Need more help

Thanks

Kumar

Answer 3

You have to implement some logic, it is not trivial:

http://www.sqlusa.com/bestpractices2005/parsename/

First of all, use | as delimiter, replace all others.

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 4

I would first change 2 of the delimiters into the same delimiter, say, space, and then will use CROSS APPLY solution and do it step by step.

Here is a sample

Parsing the FullName field to individual components

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

Can anyone please give me a solution using reverse and len function for the question below.

if the data in the phone number column is like 1234567890x456 , i want to separate  (PhonenumberXextension) into phonenumber, extension into 2 columns.  

Below is the solution works only for specific data, can anyone give solution for general data like if phone number extension have 4 digits below solution doesn't work.

select PhoneNumber, substring(phonenumber,1,patindex('%x%',phonenumber)-1) [phone Number]

,substring(phonenumber,patindex('%x%',phonenumber)+1,len(phonenumber)) [Extension] from stars where PhoneNumberlike '%x%'

Thanks

Kumar

Answer 6

its working. can you show a sample which is not working?

select PhoneNumber, substring(phonenumber,1,patindex('%x%',phonenumber)-1) [phone Number]

,substring(phonenumber,patindex('%x%',phonenumber)+1,len(phonenumber)) [Extension] from stars where PhoneNumberlike '%x%'

Thanks

Regards
Satheesh

Answer 7

Thank you satheesh for the solution.

Regards

kumar

Answer 8

I didn't change anything in the query you posted and you  said it was not working.  

Is it working now?

Regards
satheesh

Answer 9

yes its working