none
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

    Thursday, January 24, 2013 11:44 AM

Answers

  • 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


    Friday, January 25, 2013 7:15 AM

All replies

  • 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

    Thursday, January 24, 2013 12:23 PM
  •  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

    Thursday, January 24, 2013 11:18 PM
  • 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

    Friday, January 25, 2013 1:07 AM
  • 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

    Friday, January 25, 2013 1:13 AM

  • 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

    Friday, January 25, 2013 7:08 AM
  • 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


    Friday, January 25, 2013 7:15 AM
  • Thank you satheesh for the solution.

    Regards

    kumar

    Friday, January 25, 2013 7:59 AM
  • I didn't change anything in the query you posted and you  said it was not working.  

    Is it working now?

    Regards
    satheesh

    Friday, January 25, 2013 8:57 AM
  • yes its working
    Saturday, January 26, 2013 3:23 AM