field text - 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 2008 Forums

Transact-SQL (2008)

field text

Author

Topic

wided
Posting Yak Master

218 Posts

Posted - 2011-05-11 : 09:43:13

Hello

I have a field of type text

the field content:
7834 / 0008530
1000847 / 0019864
147 / 0019974

I want to extract only the data after the '/ ' no spaces

thanks

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-11 : 09:47:48

DECLARE @MyVal VARCHAR(20)
SET @MyVal ='7834 / 0008530'

SELECT SUBSTRING (@MyVal,CHARINDEX('/',@MyVal)+2,LEN(@MyVAl)-CHARINDEX('/',@MyVal))

--------------------------
http://connectsql.blogspot.com/

wided
Posting Yak Master

218 Posts

Posted - 2011-05-11 : 09:54:25

Thanks lionofdezert

it is ok

but why +2

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-11 : 10:06:19

CHARINDEX('/',@MyVal) Will return location of '/', suppose its 6 in above case but we need our string from possition 8 as we know that / will follow a space so we will also skip these two characters

--------------------------
http://connectsql.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-11 : 10:09:40

OR YOU USE
SELECT REVERSE(SUBSTRING(REVERSE(@MyVal),1,CHARINDEX(' ',REVERSE(@MyVal))-1))

--------------------------
http://connectsql.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 10:10:09

SELECT PARSENAME(REPLACE(@str,' / ','.'),1)
SELECT STUFF(@str,1,CHARINDEX(' / ',@str)+3,'')

Jim

Everyday I learn something that somebody else already knew

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-11 : 10:40:54

wow, i liked the PARSENAME idea

--------------------------
http://connectsql.blogspot.com/

wided
Posting Yak Master

218 Posts

Posted - 2011-05-11 : 10:52:56

ok
thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-12 : 03:17:16

or

select RIGHT(@str,charindex('/',@str)+1)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-12 : 03:22:23

quote:
Originally posted by jimf

SELECT PARSENAME(REPLACE(@str,' / ','.'),1)
SELECT STUFF(@str,1,CHARINDEX(' / ',@str)+3,'')

Jim

Everyday I learn something that somebody else already knew

The second method should be SELECT STUFF(@str,1,CHARINDEX(' / ',@str)+2,'')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources