Charindex - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Charindex - Substring

Author

Topic

Carpend
Starting Member

7 Posts

Posted - 2010-03-04 : 08:25:31

Hi,

I have a field with Values like the below
CCM1.AOL.BACK.I02.WAT.L1
CCM1.AOL.BACK.I02.WAT.L2
CCM1.AOL.BACK.I03.STO.L1
CCM1.AOL.BACK.I03.STO.L2
CCM1.AOL.CEO.I01.PRE.L1
CCM1.AOL.CEO.I01.PRE.L2

How do I use charindex and substring to select WAT,PRE,STO?

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-04 : 08:30:28

SELECT LEFT(RIGHT(<COLUMN>, 6), 3)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 08:31:02

[code]SELECT REVERSE(f.Val)
FROM Table t
CROSS APPLY dbo.ParseValues(REVERSE(t.field),'.')f
WHERE f.ID = 2
[/code]

Parsevalues can be found below link

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-04 : 08:37:31

Is using charindex() and substring() a "must have"?

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-04 : 08:40:10

If the length is not fixed then
use this

DECLARE @str AS VARCHAR(20)
SET @str = 'CCM1.AOL.CEO.I01.PRE.L2'
SELECT REVERSE(LEFT(REVERSE(@str), CHARINDEX('.', REVERSE(@str))- 1))

Vabhav T

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-04 : 08:42:41

yeah -- neither Visakh16's solution or mine does. Visakh16's parsevalues does use PATINDEX which is probably the closest match,

Homework question?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-04 : 09:08:47

quote:
Originally posted by vaibhavktiwari83

If the length is not fixed then
use this

DECLARE @str AS VARCHAR(20)
SET @str = 'CCM1.AOL.CEO.I01.PRE.L2'
SELECT REVERSE(LEFT(REVERSE(@str), CHARINDEX('.', REVERSE(@str))- 1))

Vabhav T

Men!
I was banging my had because I could not see how this will work correctly!

Now I have seen the reason:
VARCHAR(20) is cutting the '.L2'
and I think that isn't the right way.

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-04 : 09:12:01

Yes webfred you are correct but
Varchar(20) i have taken for example because actally he has to do same thing with the column value.
code can be used with column name.

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:13:51

quote:
Originally posted by vaibhavktiwari83

If the length is not fixed then
use this

DECLARE @str AS VARCHAR(20)
SET @str = 'CCM1.AOL.CEO.I01.PRE.L2'
SELECT REVERSE(LEFT(REVERSE(@str), CHARINDEX('.', REVERSE(@str))- 1))

Vabhav T

The values get truncated for first 20 characters in your method

Try this and see what happens

DECLARE @str AS VARCHAR(30)
SET @str = 'CCM1.AOL.CEO.I01.PRE.L2'
SELECT REVERSE(LEFT(REVERSE(@str), CHARINDEX('.', REVERSE(@str))- 1))

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:14:40

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:15:07

If 'CCM1.AOL.' is fixed, use

DECLARE @str AS VARCHAR(30)
SET @str = 'CCM1.AOL.CEO.I01.PRE.L2'
select parsename(stuff(@str,1,9,''),2)

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-04 : 09:15:55

But if the lenght is not fixed (your own words) then it can be something like 'CCM1.AOL.CEO.I01XXXX.PRE.L2XXX'.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:19:13

quote:
Originally posted by webfred

But if the lenght is not fixed (your own words) then it can be something like 'CCM1.AOL.CEO.I01XXXX.PRE.L2XXX'.

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

My query still works in that value too
It will fail only if there is one more part seperated by a dot

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-04 : 09:21:43

I meaned Vaibhav T's solution

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-04 : 09:22:33

I know I should reply with quotes.

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-04 : 09:24:20

Or my front end should do that for me

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:24:38

quote:
Originally posted by webfred

I know I should reply with quotes.

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

Ok. Thanks

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:26:25

quote:
Originally posted by webfred

Or my front end should do that for me

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

I actually wanted to reply that only

Pseudo-code

dim spt
spt=split(data,'.')
print spt(ubound(spt)-1)

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-04 : 09:43:45

Try this it will work fine with any size of varchar

DECLARE @str AS VARCHAR(1000)
SET @str = 'CCM1.AOL.CEO.I01.PRE.L2'
SELECT REVERSE( SUBSTRING(REVERSE(@str), charindex('.', REVERSE(@str)) + 1, 3))

Vabhav T

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-04 : 09:45:27

quote:
Originally posted by vaibhavktiwari83

Try this it will work fine with any size of varchar

DECLARE @str AS VARCHAR(1000)
SET @str = 'CCM1.AOL.CEO.I01.PRE.L2'
SELECT REVERSE( SUBSTRING(REVERSE(@str), charindex('.', REVERSE(@str)) + 1, 3))

Vabhav T

works only if the needed part is always of lenght=3

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-04 : 09:57:51

Hi webfred,

You must be loving this. payback time!

For some reason it's often you that seems to get this kind of attention.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources