Find string with spaces and replace them with other string

• shamshad.ali

  Hall of Fame

  Points: 3850

  More actions

  September 8, 2015 at 4:30 am

  #321008

  I have following query which return me SP/Views and Functions script using:

  select DEFINITION FROM .SYS.SQL_MODULES

  Now, the result looks like

  Create proc

  create procedure

  create proc

  create view

  create function

  I need its result as:

  Alter Procedure

  Alter Procedure

  Alter Procedure

  Alter View

  Alter Function

  I used following

  select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULES

  to but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?

  Shamshad Ali

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  September 8, 2015 at 6:15 am

  #1825083

  shamshad.ali (9/8/2015)

  ---

  I have following query which return me SP/Views and Functions script using:

  select DEFINITION FROM .SYS.SQL_MODULES

  Now, the result looks like

  Create proc

  create procedure

  create proc

  create view

  create function

  I need its result as:

  Alter Procedure

  Alter Procedure

  Alter Procedure

  Alter View

  Alter Function

  I used following

  select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULES

  to but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?

  Shamshad Ali

  Really, REALLY, [font="Arial Black"]REALLY [/font]bad idea. If you did such a thing to my very well formatted code, I'd chase you with a bat until your heels smoked.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 8, 2015 at 6:20 am

  #1825086

  This might work as a different approach. Comments before the CREATE statement might give wrong results, but should work better than your previous option.

  SELECT STUFF( definition, CHARINDEX('CREATE ',definition), 6, 'ALTER') AS definition

  FROM sys.sql_modules

  THIS IS NOT FOR AN AUTOMATED PROCESS. You still need to review the code to prevent errors.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• andreakreif

  Newbie

  Points: 7

  More actions

  September 8, 2015 at 7:10 am

  #1825107

  This will also replace all temp table creates.

  CREATE TABLE #SomeTable becomes ALTER TABLE #SomeTable.

  Like Luis said, this is not an automated process.

  Seems like regular expressions with patindex might work. I haven't been able to figure it out though.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 8, 2015 at 8:12 am

  #1825149

  andreakreif (9/8/2015)

  ---

  This will also replace all temp table creates.

  CREATE TABLE #SomeTable becomes ALTER TABLE #SomeTable.

  Like Luis said, this is not an automated process.

  Seems like regular expressions with patindex might work. I haven't been able to figure it out though.

  Neither code would affect the definition of temp tables. My code will only change the first occurence of the word create and the original code would only change it for procedures and views.

  Patindex won't be able to identify that there would only be spaces between the CREATE and the object type.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  September 8, 2015 at 11:35 am

  #1825224

  There's an undocumented function that could help you here. It's called fn_replgetparsedddlcmd and is used by replication. Basically, it parses the DDL commands and strips away the CREATE part, that you can replace with watever you want.

  select 'ALTER ' +

  typename + ' ' +

  QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' +

  QUOTENAME(OBJECT_NAME(object_id)) + char(13) + char(10) +

  master.sys.fn_replgetparsedddlcmd(

  definition

  ,'CREATE'

  ,typename

  ,DB_NAME()

  ,OBJECT_SCHEMA_NAME(object_id)

  ,OBJECT_NAME(object_id)

  ,NULL

  ) AS statement

  FROM (

  SELECT sm.definition,

  sm.object_id,

  CASE so.type

  WHEN 'V' THEN 'VIEW'

  WHEN 'P' THEN 'PROCEDURE'

  WHEN 'FN' THEN 'FUNCTION'

  WHEN 'TF' THEN 'FUNCTION'

  WHEN 'IF' THEN 'FUNCTION'

  WHEN 'TR' THEN 'TRIGGER'

  END AS typename

  FROM sys.sql_modules AS sm

  INNER JOIN sys.objects AS so

  ON sm.object_id = so.object_id

  WHERE is_ms_shipped = 0

  ) AS data

  Hope this helps

  -- Gianluca Sartori