Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Substring???

  1. #1
    Join Date
    Apr 2004
    Posts
    10

    Unhappy Unanswered: Substring???

    We have entries like below in a table. I need a query by which I can get the CustomerName from the below entries.


    A\CustomerName
    A\CustomerName\Credit
    A\CustomerName\Debit

    Thanks!

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    assuming the entries are found in tbl field customer:

    select replace(replace(replace(customer, 'A\', ''), '\Credit', ''), '\Debit', '')
    from tbl

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I hope he doesn't have any customers whose name ends in "a".

    Here is a general solution that uses the following assumptions:
    A) The customer name is between the first and second "\" marks.
    B) The customer's names don't contain slashes.
    C) The maximum string length is 500 characters (adjust as necessary).

    select
    left(substring([CustomerName], charindex('\', [CustomerName])+1, 500), isnull(nullif(charindex('\', substring([CustomerName], charindex('\', [CustomerName])+1, 500)), 0),500)-1)
    from YourTable

    If there are additional data restrictions, you will need to post them to get the best solution.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    good catch...beer helped?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That was just a half-beer solution.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2004
    Posts
    10
    Thanks for the reply. Well the customer name is not always between '\'.
    Below is a sample of some data in our table. I have to populate another column right next to the column below with the customer name. Also for all the "Entities\1" entries I need to have all the customers starting with 1 next to it. But that will be not easy to do as you will also need an entities\1 entry in the first column. So basically to do this right I need to generate two columns one with the below folder structure and one with all the customer name. So the final output should look something like this. The list goes all the way till Z.

    Output:
    Column 1 Column2
    Entities\1 1st Alliance XXI
    Entities\1\1st Alliance XXI 1st Alliance XXI
    Entities\1\1st Alliance XXI\Credit 1st Alliance XXI
    entities\a a.o. smith
    entities\a aaec
    entities\a ABBCO Community
    entities\a\a.o. smith\Credit a.o. smith
    entities\a\aaec aaec
    entities\a\aaec\ATM-Debit aaec
    entities\a\aaec\Credit aaec
    entities\a\ABBCO Community\ATM-Debit ABBCO Community
    entities\a\ABBCO Community\Credit ABBCO Community


    Data:



    Entities\1
    Entities\1\1st Alliance XXI FCU
    Entities\1\1st Alliance XXI FCU\Credit
    entities\a
    entities\a\a.o. smith efcu\Credit
    entities\a\aaec cu
    entities\a\aaec cu\ATM-Debit
    entities\a\aaec cu\Credit
    entities\a\ABBCO Community CU\ATM-Debit
    entities\a\ABBCO Community CU\Credit
    entities\a\Abbeville Seaboard CU\ATM-Debit
    entities\a\Abbott Labs CU\CO-OP
    entities\a\AC Jaacks CU\ATM-Debit
    entities\a\Academic ECU
    entities\a\Academic ECU\Credit
    entities\a\Acadian FCU\ATM-Debit
    entities\a\Adams County CU
    entities\a\Adams County CU\ATM-Debit
    entities\a\Adams County CU\Credit
    entities\a\AGRICO Chem FCU
    entities\a\AGRICO Chem FCU\Credit
    Entities\B
    Entities\B\BAE CU
    Entities\B\BAE CU\ATM-Debit
    Entities\B\BAE CU\CO-OP
    Entities\B\Bailey Controls FCU
    Entities\B\Bailey Controls FCU\Credit
    Entities\B\Bakelite Ottawa Plastics CU
    Entities\B\Bakelite Ottawa Plastics CU\ATM-Debit
    Entities\B\Bakelite Ottawa Plastics CU\Credit
    Entities\B\Balance Rock CU
    Entities\B\Balance Rock CU\ATM-Debit
    Entities\B\Balance Rock CU\CO-OP
    Entities\B\Baptist ECU
    Entities\B\Baptist ECU\Credit
    Entities\B\Barta-Berks Employees FCU
    Entities\B\Barta-Berks Employees FCU\Credit
    Last edited by heyuwassup; 04-09-04 at 11:40.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't get it...

    If you want to parse things out, you could use CHARINDEX...

    But your result set seems pecularily odd based on your base data....

    Give me one base row, and tell me how it should look after the operation...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2004
    Posts
    10
    Check Attached
    Attached Files Attached Files

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    WHY are you planning to store your data like this?

    I'm not sure if I can in good conscience assist you in doing this. It would be like a doctor violating the hypocratic oath. I'd feel like the Dr. Kevorkian of databases.

    I had assumed that you were getting the data in this format and were trying to store it in a more normalized form.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2004
    Posts
    10
    We have this new product which uses database security. And basically we are giving permission to each customer name at each folder level. Unfortunately the gui they have provided is very combursome so we went directly into the database and figured out how they are controlling security.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I could probably figure out how to parse this out...

    But again...give me a sample of a source row and what it should look like afterwards...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Apr 2004
    Posts
    10
    Thanks Brett!

    Please see the atatchment from my post at "08:58". Hope that helps.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm having zip registration problems right now...

    My guess is that'll a user defined function is what you'll end up with....

    Just supply the column name, and how dar down the "tree" you want to navigate through too...

    Then loop in the function based on the delimiter..you could even supply that....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Mar 2004
    Posts
    45
    Code:
    select a.folder,
    isnull(replace(reverse(parsename(replace(replace(reverse(a.folder),'.','|'),'\','.'),3)),'|','.'),x.name) name
    from yourtable a left join
    (select distinct substring(folder,10,1) chr,
     replace(reverse(parsename(replace(replace(reverse(folder),'.','|'),'\','.'),3)),'|','.') name
     from yourtable
     where replace(reverse(parsename(replace(replace(reverse(folder),'.','|'),'\','.'),3)),'|','.') is not null) x
    on substring(a.folder,10,1) = x.chr
    and replace(reverse(parsename(replace(replace(reverse(a.folder),'.','|'),'\','.'),3)),'|','.') is null
    Or this may be faster:

    Code:
    select a.folder, isnull(a.name,x.name) name from
    (select folder,
     replace(reverse(parsename(replace(replace(reverse(folder),'.','|'),'\','.'),3)),'|','.') name,
     substring(folder,10,1) chr
    from yourtable) a left join
    (select distinct substring(folder,10,1) chr,
     replace(reverse(parsename(replace(replace(reverse(folder),'.','|'),'\','.'),3)),'|','.') name
     from yourtable
     where replace(reverse(parsename(replace(replace(reverse(folder),'.','|'),'\','.'),3)),'|','.') is not null) x
    on a.chr = x.chr and a.name is null
    Hans.

  15. #15
    Join Date
    Apr 2004
    Posts
    10
    Hans Gives me all nulls in the customername field

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •