Update field after stiping brackets from data

Question

Post reply

Update field after stiping brackets from data

Armani

SSC Rookie

Points: 34
More actions
January 12, 2010 at 9:45 am

#137482

Hello
I have a field that contains data in below format
{FFFFFFFF-0000-0000-0000-000000000463}
{FFFFFFFF-0000-0000-0000-000000000463}
{FFFFFFFF-0000-0000-0000-000000000463}
{FFFFFFFF-0000-0000-0000-000000001576}
{FFFFFFFF-0000-0000-0000-000000001576}
{FFFFFFFF-0000-0000-0000-000000000606}
{FFFFFFFF-0000-0000-0000-000000000850}
{FFFFFFFF-0000-0000-0000-000000000850}
{FFFFFFFF-0000-0000-0000-000000001576}
and i would like to strip { and } from begining and end of the string and store the data.
How can i do this in a query?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

DECLARE @test-2 varchar(50)

SET @test-2 = '{FFFFFFFF-0000-0000-0000-000000000463}'

PRINT REPLACE(REPLACE(@test, '{', ''), '}', '')

PRINT SubString(@test, 2, len(@test)-2)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Are talking about stripping the { and } from the data and replacing it in the same place?

Paul White SSC Guru Points: 150468 More actions · Answer 3

If the column is one of the string types, all you need is CONVERT(UNIQUEIDENTIFIER, column_name).

That will remove the braces. CONVERT once more back to CHAR or whatever if you need to.

DECLARE @test-2 UNIQUEIDENTIFIER;

SET @test-2 = '{FFFFFFFF-0000-0000-0000-000000000463}';

PRINT @test-2;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

LutzM SSC Guru Points: 107049 More actions · Answer 4

I would try the two methods below and check which one runs faster.

If the length of your data will always be the same then you might not need to use then LEN function, which probably makes the last statement the fastest [edit] of the three solutions below[/edit]...

declare @x varchar(50)

SET @x='{FFFFFFFF-0000-0000-0000-000000000463}'

SELECT replace(replace(@x,'{',''),'}','')

SELECT substring(@x,2,len(@x)-2)

SELECT substring(@x,2,36)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 5

I was rather assuming that the data is all string representations of a GUID. If so, I would be amazed if a double convert wasn't the absolute fastest way:

DECLARE @test-2 VARCHAR(50);

SET @test-2 = '{FFFFFFFF-0000-0000-0000-000000000463}';

SELECT CONVERT(CHAR(36), CONVERT(UNIQUEIDENTIFIER, @test-2));

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

LutzM SSC Guru Points: 107049 More actions · Answer 6

You're definitely right...

Should have limited "fastest" to the solutions provided in the post.

I'll go and edit it... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Armani SSC Rookie Points: 34 More actions · Answer 7

below did it

update dbo.testtable set testcode = substring(testcode,1,len(testcode)-1)

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 8

Armani (1/12/2010)
below did it
update dbo.testtable set testcode = substring(testcode,1,len(testcode)-1)
Thanks

That's really strange...

Your query only removes the last character, but not the first one:

declare @x varchar(50)

SET @x='{FFFFFFFF-0000-0000-0000-000000000463}'

SELECT substring(@x,1,len(@x)-1) --{FFFFFFFF-0000-0000-0000-000000000463

SELECT substring(@x,2,len(@x)-2) --FFFFFFFF-0000-0000-0000-000000000463

But if it's working for you, then it's just fine...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 9

How odd. :hehe:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi