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

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources