CODEWITHSUNDEEP
sqlsql-server-2005
Michael
Michael

Reputation: 50

How do I remove a quote(") from SQL string before or after inputting it into a column?

I have split a name value in one column to get the first and last name to supply in another column. Sometimes I get a quote(") in the front of or beginning of someones name. How do I remove that quote?

I've tried pragmatically but it kicks back error at the set commands for @fname and @lname. 

  DECLARE @fname VARCHAR(100)
  DECLARE @lname VARCHAR(100)
  DECLARE @recnum as INT
  DECLARE @i as int
  SELECT @recnum = count(*) FROM dbo.medcor_weeklywire
  SET @i = 1
  WHILE @i <=@recnum
  BEGIN

  SELECT @fname = LTRIM(jw_employee_fname) where [sysid] = @i
  SELECT @lname = LTRIM(jw_employee_lname) where [sysid] = @i
  SET @fname = SELECT REPLACE(@fname,'"','')
  SET @lname = SELECT REPLACE(@lname,'"','')
  update [medcor_weeklywire]
  SET [jw_employee_fname] = @fname, [jw_employee_lname] = @lname

  END

Upvotes: 0

Views: 40

Answers (2)

Michael
Michael

Reputation: 50

Well first off Michael your Select statements are wrong. Second your and idiot LOL

  DECLARE @fname VARCHAR(100)
  DECLARE @lname VARCHAR(100)
  DECLARE @recnum as INT
  DECLARE @i as int
  SELECT @recnum = count(*) FROM dbo.medcor_weeklywire
  SET @i = 1
  WHILE @i <=@recnum
  BEGIN

  SELECT @fname = LTRIM(jw_employee_fname) from medcor_weeklywire where [sysid] = @i
  SELECT @lname = LTRIM(jw_employee_lname) from medcor_weeklywire where [sysid] = @i

  update [medcor_weeklywire]

  SET [jw_employee_fname] = REPLACE([jw_employee_fname], '"',''), [jw_employee_lname] = REPLACE([jw_employee_lname],'"','')
  END

I suggest you start over and don't use a loop it's taking to long...

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

Either take the SELECT out of the second statement:

SET @fname = REPLACE(@fname,'"','')

or just merge them into one SELECT:

SELECT @fname = REPLACE(LTRIM(jw_employee_fname),'"','') where [sysid] = @i

Upvotes: 1

Related Questions