sql - stored procedure to remove quotes -


i trying write stored procedure removed leading , trailing quotes arbitrary table , column. keep getting error saying table name isn't declared.

here sp

create table [dbo].[test] (id nvarchar(20)) insert dbo.test values ('"07617966004766"') go  create procedure sp_stripdoublequotes      @tablename sysname,     @columnname sysname begin     set nocount on;      update @tablename     set @columnname = substring(@columnname, 2, len(@columnname))     left(@columnname, 1) = '"'      update @tablename     set @columnname = substring(@columnname, 1, len(@columnname)-1)     right(@columnname, 1) = '"'  end go exec [dbo].[sp_stripdoublequotes] n'[dbo].[test]', n'[id]'    select * test

here link fiddle: link fiddle

create procedure sp_stripdoublequotes      @tablename sysname,     @columnname sysname,     @sql varchar(max) begin     set nocount on;         set @sql =      'update ' + '[' + @tablename +']' +     'set' + '[' + @columnname +']' +'= substring(' +'[' + @columnname +']' +', 2, len(' +'[' + @columnname +']' +'))     left('  + '[' + @columnname +']' +', 1) = '+'''"'''     --print(@sql)     exec (@sql)      set @sql =     'update ' + '[' + @tablename +']' +     'set' + '[' + @columnname +']' +'= substring(' + '[' + @columnname +']' +', 1, len(' + '[' + @columnname + ']' +')-1)     right(' + '[' + @columnname +']' +', 1) = '+'''"'''     --print(@sql)     exec (@sql) end go exec [dbo].[sp_stripdoublequotes] n'test', n'id'  -- exec [dbo].[sp_stripdoublequotes] n'[dbo].[test]', n'[id]'

updated 2nd: added [] wrap table , column incase table , column name have whitespace in them. @sean lange , @richard

updated 3rd: @[benjamin moskovits] (xd) mentioned, if hard coded brackets, correct execute command exec [dbo].[sp_stripdoublequotes] n'test', n'id'. try add or remove brackets , print see whether syntax correct before executing it.


Comments

Post a Comment

Popular posts from this blog

Retrieving ETA (estimated time of arrival) with Google Distance Matrix API and public transit as transport mode -

Image
is possible retrieve eta google's distancematrix api (or alternative api). fyi, eta different duration, when using public transport. following example clarify request. when leave on friday @ 8.15 i'll able catch train @ 8.30 , arrive @ 9.00 am. google says duration 30 minutes. however, when leave on saturday @ 8.15 i'll able catch first train @ 9.00 , arrive @ 9.30 am. google still says duration 30 minutes, means ignore delay. google map supports functionality there api around giving me eta instead of duration. can see, distance-matrix' response no good. clarifying question little. eta estimated time of arrival , it's clear user wants know entire duration of transport (including waiting time public transport).
Read more

javascript - Confirm a form & display message if form is valid with JQuery -

i'm developing django application , use javascript in order improve website. i have form , display 2 things : confirm form before submit if form submitted, display message : 'form saved' it's first time i'm using js , need make process. this code : <form class = "form" method='post' action=''> {% csrf_token %} <br></br> {{ form.as_p}} <br></br> <button type="submit">valider</button> </form> <script> $('#form').submit(function() { var c = confirm("click ok continue?"); return c; //you can return c because true or false }); </script> and if form valid , saved : <script type="text/javascript" > $(document).on('valider', 'form.form', function(form) { var $form = $(form); $.ajax({ ...
Read more

ionic framework - Meteor - Error: Failed to execute 'insertBefore' on 'Node' -

i trying make dynamic list display using meteor, idea load more elements each time bottom of page reached. the html template following : <template name="contacts"> {{#contentfor "headertitle"}} <div class="bar bar-header bar-assertive"> <h1 class="title">contacts :</h1> </div> {{/contentfor}} {{#ionview}} {{#ioncontent}} <div class="list"> {{#each contacts}} <a class="item item-avatar" href="/contacts/{{_id}}"> <img src="/img/ico_client.png"> <h2>{{full_name}}</h2> <p>{{company}}</p> </a> {{/each}} </div> {{#if moreresults}} <div id="showmoreresults" style="margin-left: 25px;"> <span class="loading">loading...</span> </div> {{/if}} {{/ioncontent}} {{/ionview}} </te...
Read more