[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

Find and replace text

Hi there

I have this string

(title LIKE '%test%') AND (name LIKE '%metallica%') AND (FirstName LIKE '%bob%') AND (ATT LIKE '31') OR (ATT LIKE '32') AND (ATT LIKE '177') AND (ATT LIKE '131')

what I would like is some way to strip the code so that a variable called @personal holds

      @personal = (title LIKE '%test%') AND (name LIKE '%metallica%') AND (FirstName LIKE '%bob%')


--please note @personal could have (name LIKE '%metallica%') AND (FirstName LIKE '%bob%') i.e. the title is not required so it should be dynamic


and
      @rest = AND (ATT LIKE '31') OR (ATT LIKE '32') AND (ATT LIKE '177') AND (ATT LIKE '131')


AND (ATT will always be AFTER the title, name and firstname, so I hope this helps?
0
mousemat24
Asked:
mousemat24
  • 3
  • 2
1 Solution
 
LowfatspreadCommented:
this part
AND (ATT LIKE '31') OR (ATT LIKE '32') AND (ATT LIKE '177') AND (ATT LIKE '131')

doesn't logically make sense....

i'm not finding it clear what your overall requirement/intention is...
0
 
LowfatspreadCommented:
do you just want to do this?

declare @personal varchar(max),@rest varchar(max)

select @personal=left(Thestring,charindex('AND (ATT',thestring)-1)
        ,@rest=right(Thestring,len(Thestring)-charindex('AND (ATT',thestring))

  from (select rtrim('Yourstring') as Thestring
             where 'Yourstring' like '%AND (ATT%' ) as x
0
 
mousemat24Author Commented:
(title LIKE '%test%') AND (name LIKE '%metallica%') AND (FirstName LIKE '%bob%') AND (ATT LIKE '31') OR (ATT LIKE '32') AND (ATT LIKE '177') AND (ATT LIKE '131')

is pasted in as a variable, yes the SQL may be wrong and im not too worried about that. Maybe I didnt make it clear on my 1st post, really sorry.

what I want is a variable, lets call is @var1, maybe using some string manipulation like  substring etc it should hold the following:

@var1 = '(title LIKE ''%test%') AND (name LIKE '%metallica%') AND (FirstName LIKE '%bob%')'

and another variable called @var2 that will hold the following:

@var2 = 'AND (ATT LIKE '31') OR (ATT LIKE '32') AND (ATT LIKE '177') AND (ATT LIKE '131')'

Hope this makes sense, again sorry if I didnt make a good job in explaining it.
0
 
LowfatspreadCommented:
did you not understand my code?



declare @personal varchar(max),@rest varchar(max)

select @personal=left(Thestring,charindex('AND (ATT',thestring)-1)
        ,@rest=right(Thestring,len(Thestring)-charindex('AND (ATT',thestring))

  from (select rtrim('(title LIKE '%test%') AND (name LIKE '%metallica%') AND (FirstName LIKE '%bob%') AND (ATT LIKE '31') OR (ATT LIKE '32') AND (ATT LIKE '177') AND (ATT LIKE '131')') as Thestring
             where '(title LIKE '%test%') AND (name LIKE '%metallica%') AND (FirstName LIKE '%bob%') AND (ATT LIKE '31') OR (ATT LIKE '32') AND (ATT LIKE '177') AND (ATT LIKE '131')' like '%AND (ATT%' ) as x

Open in new window

0
 
mousemat24Author Commented:
Thanks Lowfatspread!!! You STAR, thats what I wanted
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now