Help with Substring - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help with Substring

Author

Topic

mke1981
Starting Member

1 Post

Posted - 2011-04-07 : 09:46:48

i have some strings of text which i would like to split before the "-" ie
AB1-text
A1-text
AK2-text

so that i only get
AB1
A1
AK2

i have the following code
SUBSTRING(QCTEST.TESTNAME, 1, CHARINDEX('-',CTEST.TESTNAME)-1)
but this isn't working. I have been looking at this for over 4 weeks and still not able to get this working.

if anyone can help i would be very greatful

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-07 : 09:55:32

[code]
case
when charindex('-',QCTEST.TESTNAME) > 0 then
left(QCTEST.TESTNAME,charindex('-',QCTEST.TESTNAME)-1)
else
QCTEST.TESTNAME
end[/code]

This does a little extra work to make sure that the query does not fail even if there is no - character in the string

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-07 : 10:14:21

If the column does not have any dot, you can use

select parsenmae(replace(col,'-','.'),2) from table

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-07 : 10:15:03

or
substring(QCTEST.TESTNAME,1,isnull(nullif(charindex('-',QCTEST.TESTNAME),0),len(QCTEST.TESTNAME))-1)

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-07 : 10:38:12

Meh! I still like my query over both of your queries

PS: Not only because it is mine, but for other reasons too.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-07 : 15:02:29

quote:
Originally posted by sunitabeck

Meh! I still like my query over both of your queries

PS: Not only because it is mine, but for other reasons too.

Yes, your solution is more readable and I like it too

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-07 : 15:58:14

My solution was so awesome that had I posted it, I would have been made MVP on the spot!

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources