November 1, 2004 at 8:16 am
Can anyone please let me know if they have a sql script which would loop through every single character of a field and find if there are any characters with ascii value > 128. Anyhelp on this will be greatly appreciated.
TIA
November 1, 2004 at 8:54 am
I used to need to strip Alpha-numerics from a certain field. I have changed it to try and fit your needs. Hope it works...
SET NOCOUNT ON
DECLARE @CurrentID int,
@MaxID int,
@OriginValue varchar(25),
@TempValue varchar(1),
@CurrentPosition int,
@Length int
SELECT @CurrentID = (SELECT MIN( RowID) FROM AP),
@MaxID = (SELECT MAX( RowID) FROM AP)
WHILE @CurrentID <= @MaxID
BEGIN
SELECT @OriginValue = ' '
SELECT @OriginValue = ISNULL( (SELECT [Value] FROM [TableName] WHERE RowID = @CurrentID), '')
IF @OriginValue <> '' -- OR @OriginValue IS NOT NULL
BEGIN
SELECT @CurrentPosition = 1
SELECT @Length = (SELECT LEN( RTRIM( LTRIM( @OriginValue))))
WHILE @CurrentPosition <= @Length
BEGIN
SELECT @TempValue = (SELECT SUBSTRING( @OriginValue, @CurrentPosition, 1))
IF @TempValue = CHAR(128)
BEGIN
-- DO SOMETHING HERE...
END
SELECT @CurrentPosition = @CurrentPosition + 1
END
END
I wasn't born stupid - I had to study.
November 1, 2004 at 8:55 am
oops... should have been IF @TempValue > 128
Sorry...
I wasn't born stupid - I had to study.
November 1, 2004 at 9:05 am
Not too sure what you want to retrieve but this will return yes/no on a character by character basis
declare @i as integer
declare @mystring as nvarchar(50)
set @mystring = 'aad+asa'
set @i = 0
while @i < (len(@mystring)+1)
begin
set @i = @i + 1
if (select unicode(substring(@mystring,@i,1))) < 128
begin
print 'yes'
end
else
if (select unicode(substring(@mystring,@i,1))) > 128
begin
print 'no'
end
end
November 1, 2004 at 12:25 pm
What i am trying to get from a table is all rows of a specific field say name which has any character with ascii>128 . Any script that can help me get the results.
TIA
November 1, 2004 at 12:30 pm
If you will look at my script, I have identified all records from a field in a table that have character 128 and above. I did not know what you wanted to do with this information, but where I have written: -- Do something here. You could simply select that record based upon the ID from your table, (I used an old script of mine and the table I used was called AP).
I wasn't born stupid - I had to study.
November 2, 2004 at 6:07 am
This will do it for you but is not too efiificent:
------------------------------------------------------------
declare @i as integer
declare @mystring as nvarchar(255)
create table #tmp_results
(results nvarchar(255))
DECLARE column_name_cursor CURSOR FOR
SELECT column_name FROM table_name
OPEN column_name_cursor
FETCH NEXT FROM column_name_cursor
INTO @mystring
WHILE @@FETCH_STATUS = 0
BEGIN
set @i = 0
while @i < (len(@mystring)+1)
begin
set @i = @i + 1
if (select unicode(substring(@mystring,@i,1))) > 128
begin
insert into #tmp_results values(@mystring)
end
end
FETCH NEXT FROM column_name_cursor
INTO @mystring
END
CLOSE column_name_cursor
DEALLOCATE column_name_cursor
select distinct(results) from #tmp_results
drop table #tmp_results
--------------------------------------------------------------
November 2, 2004 at 7:18 am
Superfast set based query (0.093 sec for a table of 10000+ records of 40-50 characters):
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare @i as int
set @i = 0
while @i < 8000
begin
Insert into dbo.Numbers Default Values
set @i = @i + 1
end
Select Distinct O.PkObjSQL, O.Name from dbo.ObjSQL O cross join dbo.Numbers N
Where N.PkNumber 128
This select makes a cross join for each PkNumber that is smaller or equal to the lenght of the string, then it uses the PkNumber to do the substring and test each character for an ascii number bigger than 128.
August 31, 2007 at 3:32 am
Just to say that I found Jonathan Stokes code very useful. Thanks!
August 31, 2007 at 9:13 pm
Johnathan's code is good... but check the performance and resource usage when compared to the numbers-table solution that Remi squeezed off...
Hey Remi... nice loop on the table creation
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2007 at 7:17 am
11/2/2004
I was still in my first months then .
September 1, 2007 at 7:27 am
Heh... sorry about that... saw the recent post and didn't check the dates on the history.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2007 at 7:30 am
NP. Those are good memories. Makes me see how much I've grown in the last 2 years.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply