• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

SQL Query Parse String

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
holemania
Asked:
holemania
  • 3
  • 2
2 Solutions
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
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
 
SheilsCommented:
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
Industry Leaders: 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!

 
ajb2222Commented:
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
 
SheilsCommented:
See example in attached mdb query
dbHyphenSplitter.mdb
0
 
cyberkiwiCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now