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
 get data from string

Author  Topic 

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-01-14 : 11:26:46
My Table Column have below data
Name of my Country is India
I am from Country Japan
My Country is China

How to get result as blow
"Country is India"
"Country Japan"
"Country is China"

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-14 : 14:01:59
You'll need to elaborate on the rules to be applied but look up CHARINDEX and PATINDEX in BOL for ideas on how to search for a string (pattern) within a string.

=================================================
A man is not old until regrets take the place of dreams. - John Barrymore
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-01-14 : 14:50:02
The result is
select substring( Column ,PATINDEX ( '%country%' ,Column ),LEN(Column ))
from Table


THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-15 : 05:56:05
quote:
Originally posted by shanmugaraj

My Table Column have below data
Name of my Country is India
I am from Country Japan
My Country is China

How to get result as blow
"Country is India"
"Country Japan"
"Country is China"

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com


Simpy this

SELECT STUFF(Column,1,PATINDEX ( '%country%' ,Column ),'')
FROM Table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2014-01-16 : 08:31:19
Hi Shanmugraj,
Above given query by visakh works fine but with little bit code modification. it is not like that fully rewritten while practicing i got to know

here with an example
declare @data table
(Data VARCHAR(MAX))
INSERT INTO @data (Data) values ('Name of my Country is India')
INSERT INTO @data (Data) values ('I am from Country Japan')
INSERT INTO @data (Data) values ('My Country is China')

select STUFF(Data,1,PATINDEX ( '%country%' ,Data )-1,' ') from @data
or
you can do like this also

select SUBSTRING(Data,PATINDEX ( '%country%' ,Data )-1,LEN(data)) from @data
P.V.P.MOhan
Go to Top of Page
   

- Advertisement -