• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

Remove last quoted identifier in a table column

Hello ,

I need to remove the last quoted identifier (")in my table column.
I have quoted identiers at the beginning and the end of the values in the column but I need to remove only the last ones . Pls can some one show me how to use an update statement or something to do this in SQL

Many thanks

0
marvo2010
Asked:
marvo2010
3 Solutions
 
Pratima PharandeCommented:
try this

update tablename
set colname = LEFT(colname, (Len(colname)-1))
0
 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
This would help...
DECLARE @string varchar(50)
SET @string='some string"'
select LEFT(@string,LEN(@string)-1)

Open in new window

0
 
Daniel_PLDB Expert/ArchitectCommented:
Check this script, it'll show you whole process:
 
USE [tempdb]
--create test table
CREATE TABLE #test
(id INT IDENTITY(1,1),
data VARCHAR(32))

--load random data to test table
DECLARE @a TINYINT
SET @a=0
WHILE @a <= 200
BEGIN
	DECLARE @Length TINYINT
	DECLARE @RandomID varchar(32)
	DECLARE @counter smallint
	DECLARE @RandomNumber float
	DECLARE @RandomNumberInt tinyint
	DECLARE @CurrentCharacter varchar(1)
	DECLARE @ValidCharacters varchar(255)
	SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-=+&$'
	DECLARE @ValidCharactersLength int
	SET @ValidCharactersLength = len(@ValidCharacters)
	SET @CurrentCharacter = ''
	SET @RandomNumber = 0
	SET @RandomNumberInt = 0
	SET @RandomID = ''
	SET @Length = 30
	SET NOCOUNT ON
	SET @counter = 1
	WHILE @counter < (@Length + 1)
		BEGIN
			SET @RandomNumber = Rand()
			SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))
			SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)
			SET @counter = @counter + 1
			SET @RandomID = @RandomID + @CurrentCharacter
		END
	SET @RandomID='"'+@RandomID +'"'
	INSERT INTO #test VALUES (@RandomID)
	SET @a=@a+1
END

--select data from test table
SELECT * FROM #test

--update each row in test table by removing last ["] from string
UPDATE #test
SET data = LEFT(data,PATINDEX('%["]',data)- 1)

--see what was removed
SELECT * FROM #test

--drop test table
DROP TABLE #test

Open in new window

0
 
marvo2010Author Commented:
Thanks Guys . Two of you gave me the best replies that led me to the solution so I am going to share the points between both of . I do appreciate your help immensely.

Thanks alot

0
 
marvo2010Author Commented:
Actually three of you . Thanks
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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