Return data to the left of a dash

[FranS37]

I have data that has data before and after a dash. The length of the data is variable. For example:

AA11111-000
AA1111-000

I want to return the data to the left of the dash only.

Thanks for any help.

 

[johnherman]

The INSTRING, INDEX, or in T-SQL CHARINDEX expression will help you find the location of the dash. Then you can use SUBSTRING to get the string from position 1 to the position returned by the CHARINDEX function.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[softhemc]

However, although I know the gurus here would frown on this, I would be tempted to REPLACE the '-' with '.' and then use PARSENAME. It's great for extracting upto four different elements from a string, but needs the '.' as a delimiter.

 

[FranS37]

Yes, I've tried INSTRING, CHARINDEX, SUBSTRING. I'm looking for examples.

Thanks.

 

[Olaf Doschke]

Look at [tt]SELECT CHARINDEX('-',field) FROM yourtable[/tt], then [tt]SELECT SUBSTRING(field,1,CHARINDEX('-',field)) FROM yourtable[/tt] and finally [tt]SELECT SUBSTRING(field,1,CHARINDEX('-',field)-1) FROM yourtable[/tt].

I assume the last one will error on some row having no dash. The previous to last one might fail on NULLs.

Bye, Olaf.

 

[FranS37]

Thank you, OlafDoschke.

SELECT SUBSTRING(myfield,1,CHARINDEX('-',myfield))
FROM mytable

This works, with one minor thing. It includes the '-'.

Can't seem to remove it.

 

[Olaf Doschke]

Have you read to the end and tried the next one, where one is subtracted from CHARINDEX?

Bye, Olaf.

 

[FranS37]

Yep.

Invalid length parameter passed to the LEFT or SUBSTRING function.

Bye.

 

[Olaf Doschke]

Yeah, well, now we come to the piont where I warned about. Not all data might have a dash, and then CHARINDEX is 0 and there can't be a SUBSTRING with length -1.

Common, you can fix this...

SELECT SUBSTRING(field,1,CHARINDEX('-',field)-1) FROM yourtable WHERE CHARINDEX('-',field)>0

Bye, Olaf.

 

[Olaf Doschke]

Let me summarize a bit:

I didn't simply gave you a final solution but the single steps to show you how you arrive there, I warned about the possible problems, one really happened and you even didn't notice and had no idea what to do.

I know people are typically only telling the regular case, but programming could be much easier, if that typical case wouldn't be accompanied by corner cases. You have a few possible corner cases here and data without a dash is only one corner case.

Nobody can browse more than a few rows, so use SQL to get an overview:

SELECT CHARINDEX('-',field) as DashPosition, Count(*) as Howoften FROM yourtable GROUP BY CHARINDEX('-',field)

Seeing a row with 0 as Dashposition data without a dash is revealed.

Even more cautious would be first trying with some self generated test data about expected wrong data:

Declare @yourtable as Table (field char(10) null)
insert into @yourtable values ('a-1'),('b4'),(null)

SELECT CHARINDEX('-',field) as DashPosition, Count(*) as Howoften FROM @yourtable GROUP BY CHARINDEX('-',field)

We see CHARINDEX('-',NULL) is fortunately not erroring, simply resulting in NULL. And that also gives us an overview about corner cases of NULL aside of data with no dash. Now what about data with more than one dash?

Declare @yourtable as Table (field char(10) null)
insert into @yourtable values ('a-1'),('b-4-v'),(null)

SELECT LEN(field)-LEN(REPLACE(field,'-','')) as NumberOfDashes, Count(*) as Howoften FROM @yourtable GROUP BY LEN(field)-LEN(REPLACE(field,'-',''))

You got to be a detective and determined to reach your goal as programmer. If you don't get any ideas how to use your programming skills to see whether your customer lied to you by only telling the regular case, then you can be replaced by any other developer. It's not, that customers lie by intention, they just don't think to the end, have no overview. That's your chance to earn money. You have overview, you could have, if you just do a little analysis before jumping to the task. You're not determined enough, if you don't use that.

You got more than "any help", even just by being pointed to functions by John, they are documented and you can read. Just answering you tried that functions is so typical. Then show, what you tried, and we see your deficits. Are you afraid to show deficits? Then you never will learn, what you don't discover on your own. Just seeing your wrong understanding of how functions work we can put you on the right track interactively. The help text won't answer questions.

Common, have you forgotten about the fun to find a solution? Is this a burden for you? Then how about looking for something else than programming?

Bye, Olaf.

 

[softhemc]

This returns characters to the left of the -, or NULL if there is no - or the column is NULL

SELECT 
  PARSENAME
    (REPLACE(Field1,'-','.')
    ,2) 
FROM UsingParseName

 

[softhemc]

This, however returns the contents of the field if no - is found.

SELECT 
  CASE WHEN 
    CHARINDEX('-', Field1, 1) < 1
  THEN 
    Field1
  ELSE
    PARSENAME
	 (REPLACE(Field1,'-','.')
	 ,2) 
   END
FROM UsingParseName

 

[FranS37]

So much harsh judgment As a response to a question about a function?

 

[Olaf Doschke]

What question? You were just making a statement about SUBSTRING not working. That's an implicit question at most. The error is telling you the answer, -1 is an invalid length. -1 results from data with no dash. You didn't mention data with no dash.... etc. etc.

Bye, Olaf.

 

[johnherman]

SELECT (CASE WHEN CHARINDEX ('-', yourfield) > 1 THEN SUBSTRING(yourfield, 1, CHARINDEX ('-',yourfield)-1) ELSE yourfield AS FIELD-TO-LEFT-OF-DASH) FROM yourtable.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[gmmastros]

The easiest, and most reliable way to do this is to make sure your code "finds" something. The easiest way to do this is to add a dash symbol to the end of the data.

Declare @Temp Table(Data VarChar(20));

Insert Into @Temp Values('abc-123');
Insert Into @Temp Values('xyz-789');
Insert Into @Temp Values('abc');
Insert Into @Temp Values('-123');
Insert Into @Temp Values('-');
Insert Into @Temp Values('');
Insert Into @Temp Values(null);


Select	Data As Before, 
        Left(Data, CharIndex('-', Data + '-')-1) As After.
From	@Temp

The (potential) problem is that there may not be a dash symbol in your data. By returning the position of "Data + '-'", you basically ensure that the charindex functions returns a number greater than 0 (or null, if the input is null). This prevents the second argument of the Left function from being negative (when you subtract one from it).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

Everybody makes the assumption a value without a dash would mean it's the result as is. I'll not follow that, though it's common sense.

It would make sense, if data looked like
AA11111-000
AA1111-000
AA1111441
...

It may also mean some data is actually missing a dash, where it should have one (ie the last should be AA1111-441).

If data looks like
AA11111-000
AA1111-000
000
001

It's more like the data without dash is simply missing the left part.

So I jump back to my conclusion you should analyze data, prompt your customer (if you don't do that for yourself) with findings and then "plan" what to do in the light of the analysis. I say "plan", because it may only take a phone call and is a simple decision, often enough. You should neither force a solution, unbreakable code can be like plastic - it can be worse to not reveal problems than to break.

Fran, I feel sorry for you in any case. If you only see harshness in my answers and no value in them, I just feel sorry in another sense...

As I see from your profile, you came here only a few times once and again and it seems you never get warm with tek-tips. Market leaders are other forums, eg Stack Exchange/Stack Overflow. I won't blame you, if you turn down tek-tips once more. If you wish, I can stop here and we can avoid each other. I hope we can do better, though. Think about this: If I just won't care about you, I simply could have skipped many things and just closed my eyes and cross my fingers this data change won't do any harm, even if it goes wrong. In the end it'll be fixed or as so many things, won't matter at all in the vastness of data and databases anyway, so no reason to get picky or make it a matter of principles. But I also see you do databases at least since 2002, though you did then use MySQL. So you should already know better. If not knowing some database dialect, then how to find examples and learn from them or how to get to the core language definitions.

Currently one database of interest world wide is from Panama, and not because it has some technically wrong data in it. So, yes, there are much more important things.

Bye, Olaf.