April 18, 2011 at 2:34 pm
Hi everybody, I has created a user function that replace any digit that don't been letter or number but when appear a number with superscript or subscript(ej: ¹) recognize it like a number, and I dont want this. Someone, know how delete/replace this kind of letter?
Thanks by your help!
April 18, 2011 at 2:45 pm
To receive test help, please, please post your table definition, some sample data, the results required from that sample data, and any T-SQL code you have attempted to use?. To do this click on the first link in my signature block and follow the instructions in the article which is displayed.
April 18, 2011 at 2:53 pm
Data to delete or change: JR ISMAEL ¹GADOR 298
Data to output: JR ISMAEL GADOR 298
User Function:
CREATE FUNCTION [dbo].[REMUEVE_SIMBOLOS_SE_FN](@str varchar(max))
RETURNS varchar(max)
AS
BEGIN
DECLARE @i int
DECLARE @r varchar(max)
DECLARE @C char(1)
SELECT @STR = REPLACE(REPLACE(LTRIM(RTRIM(@str)), '%', ''),'_','')
SET @i = 1
SET @r = ''
WHILE @i <= LEN(@str)
BEGIN
SET @C = SUBSTRING(@str, @i, 1)
IF '012923456789ABCDEFGHIJKLMÑNOPQRSTUVWXYZ ' LIKE '%' + @C + '%'
SET @r = @r + @C
SET @i = @i + 1
END
RETURN @r
END
April 18, 2011 at 7:01 pm
Lisset (4/18/2011)
Hi everybody, I has created a user function that replace any digit that don't been letter or number but when appear a number with superscript or subscript(ej: ¹) recognize it like a number, and I dont want this. Someone, know how delete/replace this kind of letter?Thanks by your help!
If you'd be kind enough to post your function, then I'll tell you the easy way to do this and it'll likely speed up your function, as well.
--Jeff Moden
April 19, 2011 at 2:16 am
Jeff Moden (4/18/2011)
Lisset (4/18/2011)
Hi everybody, I has created a user function that replace any digit that don't been letter or number but when appear a number with superscript or subscript(ej: ¹) recognize it like a number, and I dont want this. Someone, know how delete/replace this kind of letter?Thanks by your help!
If you'd be kind enough to post your function, then I'll tell you the easy way to do this and it'll likely speed up your function, as well.
Looks to me like the function definition appears immediately above your post Jeff 🙂
I'm still uncertain on the details of the problem.
Are you saying that 2 is retained, but ² (superscript 2) is removed? Or not? Following on from that, do you want it to be removed?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 19, 2011 at 7:04 am
I suspect it is your collation set to 2000 default LATIN1_General_CI_AS
Try changing the collation during the check, ie
IF @C COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '[0-9A-Z ]' COLLATE SQL_Latin1_General_CP1_CI_AS
Note that I changed the LIKE clause to shorten it
I'll leave the performance issue to Jeff (don't want to steal his thunder :-D)
Far away is close at hand in the images of elsewhere.
Anon.
April 19, 2011 at 8:46 am
Thanks a lot David! Your solution is correct!
April 20, 2011 at 7:01 am
Phil Parkin (4/19/2011)
Jeff Moden (4/18/2011)
Lisset (4/18/2011)
Hi everybody, I has created a user function that replace any digit that don't been letter or number but when appear a number with superscript or subscript(ej: ¹) recognize it like a number, and I dont want this. Someone, know how delete/replace this kind of letter?Thanks by your help!
If you'd be kind enough to post your function, then I'll tell you the easy way to do this and it'll likely speed up your function, as well.
Looks to me like the function definition appears immediately above your post Jeff 🙂
I'm still uncertain on the details of the problem.
Are you saying that 2 is retained, but ² (superscript 2) is removed? Or not? Following on from that, do you want it to be removed?
Yowch! Must have been a tough day. I totally missed it. David is spot on with what I was going to do... nice high speed collation (although a binary one might be a bit faster). Well done David! Sorry I'm asleep at the switch on this one.
--Jeff Moden
April 20, 2011 at 7:06 am
David Burrows (4/19/2011)
(don't want to steal his thunder :-D)
BWAA-HAA!!! Considering how badly I missed that the OP had posted the function I requested, there may be no thunder so steal.
I'll have to get back on the possible performance issue.
--Jeff Moden
April 20, 2011 at 7:08 am
Lisset (4/18/2011)
Data to delete or change: JR ISMAEL ¹GADOR 298Data to output: JR ISMAEL GADOR 298
User Function:
CREATE FUNCTION [dbo].[REMUEVE_SIMBOLOS_SE_FN](@str varchar(max))
RETURNS varchar(max)
AS
BEGIN
DECLARE @i int
DECLARE @r varchar(max)
DECLARE @C char(1)
SELECT @STR = REPLACE(REPLACE(LTRIM(RTRIM(@str)), '%', ''),'_','')
SET @i = 1
SET @r = ''
WHILE @i <= LEN(@str)
BEGIN
SET @C = SUBSTRING(@str, @i, 1)
IF '012923456789ABCDEFGHIJKLMÑNOPQRSTUVWXYZ ' LIKE '%' + @C + '%'
SET @r = @r + @C
SET @i = @i + 1
END
RETURN @r
END
Hi Lisset,
My apologies for missing your post above. I do have a question before possibly offering an alternative to your code above... do you ever actually use it for anything longer than a VARCHAR(8000)?
--Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply