Checking for a character
-
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
-
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.
-
oops... should have been IF @TempValue > 128
Sorry...
I wasn't born stupid - I had to study.
-
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
------------------------------
The Users are always right - when I'm not wrong! -
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
-
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.
-
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
--------------------------------------------------------------
------------------------------
The Users are always right - when I'm not wrong! -
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.
-
Just to say that I found Jonathan Stokes code very useful. Thanks!
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
11/2/2004
I was still in my first months then
. -
Heh... sorry about that... saw the recent post and didn't check the dates on the history.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
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