return RIGHT of string until CHARINDEX

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)

return RIGHT of string until CHARINDEX

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2011-01-07 : 12:12:26

Is there a way to return the right part of a string untill a certain character is encountered.

In the sample data below i know a freetext field is at the end of my string and i want to separate this ou as exampled below.

Any advice would be great!
Thanks

---------------------------------------------
/*
Please paste T-SQL into query window
*/
---------------------------------------------

CREATE TABLE #sample

(
Field1 nvarchar(100)
)

Insert into #sample
select '#"XPELV"#"1802"#"1006"#"04/01/11"freetext' UNION all
select '#"07/01/11"#"XCHES"#"1400"#"1400"#"07/01/11"Chest X-ray'

select * from #sample

--This is an example of the desired output

CREATE TABLE #desiredoutput

(
Field1 nvarchar(100)
)
insert into #desiredoutput
select 'freetext' UNION all
select 'Chest X-ray'

select * from #desiredoutput

drop table #sample
drop table #desiredoutput

DLTaylor
Posting Yak Master

136 Posts

Posted - 2011-01-07 : 12:21:17

I suppose i should add the character i encounter in my example is the " character

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-07 : 12:26:32

Maybe this?

Jim


select  field1 
                REVERSE(STUFF 
								  ( reverse(Field1)  -- into here
									 ,charindex( '"',reverse(Field1)) --starting here
									 ,100000  
									 , '' --this value 
								  )
                        )
 

 from #sample

Everyday I learn something that somebody else already knew

DLTaylor
Posting Yak Master

136 Posts

Posted - 2011-01-07 : 12:34:10

Thanks - im getting incorrect syntax near STUFF?

DLTaylor
Posting Yak Master

136 Posts

Posted - 2011-01-07 : 12:41:20

Sorry my mistake ! code works perfectly!

Thanks for your help jimf ;-)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-07 : 12:43:41