TSQL string manupulation

i want to check if the column in inserted row in the table like this format .

@msg = "age 20";
@msg=  "region new york";

i want to check the first one like age or region
than i trim the @msg and get the other part after "age " or "region " or "sex "
LVL 1
AFIF JABADOAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
awking00Connect With a Mentor Commented:
>>set @msg =  'a region  0 new york'<<
With this example, using replace would yield 'a  0 new york' when I suspect you would want '0 new york'
Perhaps something like
case
 when charindex('region ',@msg) > 0
 then substring(@msg,charindex('region ',@msg) + 7,len(@msg))
 when charindex('age ',@msg) > 0
 then substring(@msg,charindex('age ',@msg) + 4,len(@msg))
 when charindex('sex ',@msg) > 0
 then substring(@msg,charindex('sex ',@msg) + 4,len(@msg))
end
0
 
Ross TurnerManagement Information Support AnalystCommented:
Here is an example

Just use case statements to check, in the example i split it into two columns just for easy reading

http://sqlfiddle.com/#!3/d41d8/14651

Declare @msg1 varchar(30) 
Set @msg1 = 'age 20'

Declare @msg varchar(30) 
set @msg =  'region new york'


SELECT 
Case 
when CHARINDEX('region',@msg) > 0  then REPLACE (@msg ,'region','')
else @msg
end as Test,
Case 
when CHARINDEX('age',@msg1) > 0  then REPLACE (@msg1 ,'age','')
else @msg1
end as Test1

Open in new window

0
 
AFIF JABADOAuthor Commented:
Declare @msg1 varchar(30)
Set @msg1 = 'age 20'

Declare @msg varchar(30)
set @msg =  'a region  0 new york'


SELECT
Case
when CHARINDEX('region',@msg) > 0  then REPLACE (@msg ,'region','')
else @msg
end as Test,
Case
when CHARINDEX('age',@msg1) > 0  then REPLACE (@msg1 ,'age','')
else @msg1
end as Test1




test this one the message should be start with Region !!!!!!
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Ross TurnerManagement Information Support AnalystCommented:
if you could show the data before and how you want it to look after we can knock something up for you

the more info the better
0
 
AFIF JABADOAuthor Commented:
look , @msg = 'region XXX XXXX'
or @msg = 'age XX XX'

if @msg start with region take the second part after the space

begin

end

else if @msg start with age take the second part after the space

begin


end
0
 
Ross TurnerManagement Information Support AnalystCommented:
You mean something like this

http://sqlfiddle.com/#!3/05aa5/2
or
http://sqlfiddle.com/#!3/f974a/1

SELECT 
Case 
when CHARINDEX('region',Msg) > 0  then REPLACE (Msg ,'region','')
when CHARINDEX('age',Msg) > 0  then REPLACE (Msg ,'age','')
else msg
end as Test
from 
TEST

Open in new window

0
 
PaulCommented:
Is it just the 3 strings and no more? ("age " or "region " or "sex ")
and always with a space after?
are they 'mutually exclusive'? i.e. this does not occur 'Region Australia Age Young-at-heart'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.