Link to home
Create AccountLog in
Avatar of holemania
holemania

asked on

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?

ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
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
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
See example in attached mdb query
dbHyphenSplitter.mdb
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.