Solved

SQL Query Parse String

Posted on 2010-09-13
6
530 Views
Last Modified: 2012-05-10
Hello experts,

I have a field that is a string of text divided by a hypen. See example:

1-LGX-AH-M-BY

Pull LGX.

I need to pull the letters after the first hyphen only.  Letters can be in 2, 3, 4, or whatever.

23-GHLKKS-AH-882-BM

Pull GHLKKS.

I tried substring, but then it would only pull certain characters with a set position.  Was thinking of using charindex, but I can't seem to get it to work correctly..  Can someone provide me a query to pull letters after the first hyphen and nothing after the 2nd hyphen?

0
Comment
Question by:holemania
  • 3
  • 2
6 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 33666503
with tbl as (
select col='1-LGX-AH-M-BY' union all
select null union all
select '23-GHLKKS-AH-882-BM' union all
select '45--nothing in between' union all
select '')

select case when col like '%-%-%' then
      substring(col, charindex('-', col)+1,
      charindex('-', col, charindex('-', col)+1) - charindex('-', col) -1)
      else '' end
from tbl
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33666509
Substitute tbl and col for your table and column name into this query

select case when col like '%-%-%' then
      substring(col, charindex('-', col)+1,
      charindex('-', col, charindex('-', col)+1) - charindex('-', col) -1)
      else '' end
from tbl
0
 
LVL 16

Expert Comment

by:Sheils
ID: 33666516
Use the Instr and left  method in access. In excel and sql replace instr by the find function

The syntax for the Find function is:

Find( text1, text2, start_position )

text1 is the substring to search for in text2.

text2 is the string to search.

start_position is the position in text2 where the search will start. The first position is 1

The syntax for the Instr function is:

Instr ( [start], string_being_searched, string2, [compare] )

start is optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1.

string_being_searched is the string that will be searched.

string2 is the string to search for.

compare is optional. This is the type of comparison to perform.

The syntax for the Mid function is:

Mid ( text, start_position, number_of_characters )

Text is the string that you wish to extract from.

Start_position indicates the position in the string that you will begin extracting from. The first position in the string is 1.

Number_of_characters indicates the number of characters that you wish to extract. If you omit this parameter, the Mid function will return all characters after the start_position.

The above syntax have been copied from: http://www.techonthenet.com/index.php

Nexting these two function in a formula will split your string, I have done it many times
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Assisted Solution

by:ajb2222
ajb2222 earned 250 total points
ID: 33666628
replace @F1 with your column name

left(substring(@F1,charindex('-',@F1)+1,len(@F1)-charindex('-',@F1)+1),charindex('-',substring(@F1,charindex('-',@F1)+1,len(@F1)-charindex('-',@F1)+1))-1)


0
 
LVL 16

Expert Comment

by:Sheils
ID: 33666721
See example in attached mdb query
dbHyphenSplitter.mdb
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33667100
Thanks for the grade.

FWIW, http:#a33666628  errors with

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

When @F1 is an empty string.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query to calculate avaerage 21 57
Trouble with <> 2 27
SQL Syntax 6 41
How to add an Index to a date/time field? 15 13
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question