Solved

SQL Query Parse String

Posted on 2010-09-13
6
528 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help - SELECT Statement 6 52
VB.NET return row from SQL to DataSet and get column values ? 3 51
SQL Syntax 5 37
get column names from table in vb.net 8 28
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

778 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