charindex help or something else

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

charindex help or something else

Author

Topic

jenam
Starting Member

13 Posts

Posted - 2005-04-26 : 16:24:06

I'm trying to isolate the tag number prior to the hyphen, but I can't seem to eliminate the hyphen itself!

The tag number may look like this ...
4016B-CF
4016B-CG
47952A-BB6
47952A-BB7

My query looks like this ...
SELECT tag_nbr,
SUBSTRING(tag_nbr, 1, CHARINDEX('-',tag_nbr)) as test
FROM tagh

My output looks like this (includes the hyphen) ...
4016B-CF 4016B-
4016B-CG 4016B-
47952A-BB6 47952A-
47952A-BB7 47952A-

How do I drop the hyphen?
Any suggestions would be appreciated!
Jena

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-04-26 : 16:27:14

SUBSTRING(tag_nbr, 1, CHARINDEX('-',tag_nbr) -1 )as test

Jim
Users <> Logic

jenam
Starting Member

13 Posts

Posted - 2005-04-26 : 16:30:55

thank you, but tag_nbr is not a number, so I get ...
Syntax error converting the varchar value '4016B-' to a column of data type int.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-04-26 : 16:36:20

Sorry I had a ) out of place

SUBSTRING(tag_nbr, 1, CHARINDEX('-',tag_nbr) -1 )as test

Jim
Users <> Logic

jenam
Starting Member

13 Posts

Posted - 2005-04-26 : 16:39:07

thank you. I had tried that too, but was welcomed with ...
Invalid length parameter passed to the substring function.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-26 : 16:42:59

What else are you trying to do, Jims statement did as you asked
Are you concatenating it

Jims statement:
SELECT SUBSTRING('4016B-CF', 1, CHARINDEX('-','4016B-CF') -1 )as test
Returns - 4016B

Concatenate with error
SELECT SUBSTRING('4016B-CF', 1, CHARINDEX('-','4016B-CF') -1 ) + 100 as test
Returns - "Syntax error converting the varchar value '4016B' to a column of data type int."

Concatenate without error
SELECT SUBSTRING('4016B-CF', 1, CHARINDEX('-','4016B-CF') -1 ) + '100' as test
Returns - 4016B100

HTH

Andy

Beauty is in the eyes of the beerholder

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-26 : 16:51:52

quote:
Originally posted by jenam

thank you. I had tried that too, but was welcomed with ...
Invalid length parameter passed to the substring function.

Does your data always have a hyphen in it?? Because if it doesnt you are in effect doing this

SELECT SUBSTRING(tag_nbr,1,-1)

Andy

Beauty is in the eyes of the beerholder

jenam
Starting Member

13 Posts

Posted - 2005-04-26 : 16:57:48

That's the crazy part ... I'm not trying to do anything else, just display it!

When I cut and paste Jim's code into Query Analyzer, the first and third statements work just fine! But, when I go against my database (PROGRESS) using the variable names, they are not so happy!

Any ideas?

jenam
Starting Member

13 Posts

Posted - 2005-04-26 : 16:59:03

Great idea, but, no, my data does not always have a hyphen.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-26 : 17:04:15

Try this, should work

SELECT SUBSTRING(tag_nbr, 1, LEN(tag_nbr) - CHARINDEX('-',REVERSE(tag_nbr))) as test

Andy

Beauty is in the eyes of the beerholder

jenam
Starting Member

13 Posts

Posted - 2005-04-26 : 17:05:38

oops, back the truck up! This is NOT going against a PROGRESS database, it's truly an SQL database (we did some data mining for performance reasons).

So, why would I get different results?

jenam
Starting Member

13 Posts

Posted - 2005-04-26 : 17:11:51

You are a genius! An absolute genius!
I simply can not say thank you enough!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-26 : 23:49:49

PROGRESS database.....this has to be some piece of crap accounting database. I HATE that piece of dung.

Well, that felt good to get out.
/RANT (second use today)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources