SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 get data from string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shanmugaraj
Posting Yak Master

219 Posts

Posted - 01/14/2014 :  11:26:46  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 01/14/2014 :  14:01:59  Show Profile  Reply with Quote
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 - 01/14/2014 :  14:50:02  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
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

India
52325 Posts

Posted - 01/15/2014 :  05:56:05  Show Profile  Reply with Quote
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

India
252 Posts

Posted - 01/16/2014 :  08:31:19  Show Profile  Reply with Quote
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

Edited by - mohan123 on 01/16/2014 08:34:22
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000