Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query Parse String

Posted on 2010-09-13
6
Medium Priority
?
534 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 1000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Assisted Solution

by:ajb2222
ajb2222 earned 1000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

618 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