charindex,unicode - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

charindex,unicode

Author

Topic

ducletan
Starting Member

25 Posts

Posted - 2011-06-06 : 22:41:05

I want:
dbo.bodau('Lê T?n') -> Le Tan
dbo.bodau('Ð?c') -> Duc
dbo.bodau('Hoàng')-> Hoang

CREATE FUNCTION dbo.bodau (
@sstring nvarchar(max)
)
RETURNS varchar(max)
AS
BEGIN
if len(@sstring)=0 return''
declare @str As table ([vn] nvarchar(max),[vnU] nvarchar(max),[vn1] char(1))
insert into @str ([vn],[VNU],[vn1]) values ('áà?ã?a?????â?????','ÁÀ?Ã?A?????Â?????','a')
insert into @str ([vn],[VNU],[vn1]) values ('éè???ê?????','ÉÈ???Ê?????','e')
insert into @str ([vn],[VNU],[vn1]) values ('íì?i?','ÍÌ?I?','i')
insert into @str ([vn],[VNU],[vn1]) values ('ý????','Ý????','y')
insert into @str ([vn],[VNU],[vn1]) values ('óò?õ?ô?????o?????','ÓÒ?Õ?Ô?????O?????','o')
insert into @str ([vn],[VNU],[vn1]) values ('úù?u?u?????','ÚÙ?U?U?????','u')
insert into @str ([vn],[VNU],[vn1]) values ('d','Ð','d')

declare @sreturn varchar(max)
declare @sreturn1 char(1)
set @sreturn=''
declare @str1 cursor
declare @ndem INT

set @ndem=1
while @ndem<=len(@sstring)

begin
select @sreturn1=ISNULL(
(select case CHARINDEX(SUBSTRING(@sstring,@ndem,1),[vn]) when 0
then ( case CHARINDEX(SUBSTRING(@sstring,@ndem,1),[vnU]) when 0 then null else upper([vn1]) END )
else [vn1] end from @str where CHARINDEX(SUBSTRING(@sstring,@ndem,1),[vn])>0 or CHARINDEX(SUBSTRING(@sstring,@ndem,1),[vnU])>0 )
,SUBSTRING(@sstring,@ndem,1))
SET @sreturn=@sreturn+@sreturn1
set @ndem=@ndem+1
end
RETURN @sreturn
END

it's not work
Please help me

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-07 : 04:36:28

How does it not work?

One issue will be that you are expecting a single row back from the charindex whereas you will get many

declare @s table (s varchar(10))
insert @s select 'a'
insert @s select 'b'

select CHARINDEX('a',s) from @s

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources