Extract part of string from a text filed

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

SQL Server 2000 Forums

Transact-SQL (2000)

Extract part of string from a text filed

Author

Topic

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2005-09-01 : 03:22:51

One table has a text field which is used html codes.
I want to extract part of the string and so I create a function listed in the followings. However, it seems that the function can't display Chinese characters correctly. What should I do?

CREATE FUNCTION dbo.ExtractAptLoc
( @Note text )
RETURNS nvarchar(200)
AS
BEGIN

Declare @StartPos int
Declare @EndPos int
DECLARE @output nvarchar(200)

select @StartPos = PATINDEX('%<loc>%', @Note) + 11
select @EndPos = PATINDEX('%</loc>%', @Note) - @StartPos

IF PATINDEX('%<loc>%', @Note) = 0 or PATINDEX('%</loc>%', @Note) = 0
BEGIN
SET @output = Null
END

ELSE

BEGIN
SET @output =Substring(@Note, @StartPos , @EndPos )
END
RETURN @output
END

Kristen
Test

22859 Posts

Posted - 2005-09-01 : 03:50:53

Should this be:


CREATE FUNCTION dbo.ExtractAptLoc
( @Note ntext )
RETURNS nvarchar(200)

in order to get Chinese charccters?
Kristen

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2005-09-01 : 04:10:32

no, it doesn't work. It sill display sth like »ÉÆrÆW¡]«È¤HofficeªñSogoªþªñ)
in Query Analyzer and Crystal report. Any tips?

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2005-09-01 : 04:27:45

quote:
Originally posted by yipchunyu

no, it doesn't work. It sill display sth like »ÉÆrÆW¡]«È¤HofficeªñSogoªþªñ)
in Query Analyzer and Crystal report. Any tips?

stange it display correctly if I paste here

¡Â»ˆYµØ©ˆy¡±ÈûÂ¡±©ˆy¼ˆ_

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2005-09-01 : 04:28:43

quote:
Originally posted by yipchunyu

quote:
Originally posted by yipchunyu

no, it doesn't work. It sill display sth like »ÉÆrÆW¡]«È¤HofficeªñSogoªþªñ)
in Query Analyzer and Crystal report. Any tips?

stange it display correctly if I paste here

¡Â»ˆYµØ©ˆy¡±ÈûÂ¡±©ˆy¼ˆ_

doesn't work again

not again?

Kristen
Test

22859 Posts

Posted - 2005-09-01 : 04:30:56

Does

SELECT TOP 10 MyNotesColumn FROM MyTable

display Chinese? If not you are perhaps missing some installed Font

Kristen

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2005-09-01 : 04:35:36

quote:
Originally posted by Kristen

Does

SELECT TOP 10 MyNotesColumn FROM MyTable

display Chinese? If not you are perhaps missing some installed Font

Kristen

I also want to check. However, the filed in the original table is text type (not ntext type) and it's a 3-party program that stored a html on it. when I query it. it display sth like (not whole text)
location notes
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
¯»ÀµØ©ú§øÂ§©ú¼Ó <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<STYLE type=text/css> P, UL, OL, DL, DIR, MENU, PRE { margin: 0 auto;}</STYLE>

<META content="MSHTML 6.00.2900.2180" name=GENERATOR></HEAD>
<BODY leftMargin=1 topMargin=1 rig

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2005-09-01 : 04:37:56

quote:
Originally posted by yipchunyu

quote:
Originally posted by Kristen

Does

SELECT TOP 10 MyNotesColumn FROM MyTable

display Chinese? If not you are perhaps missing some installed Font

Kristen

I also want to check. However, the filed in the original table is text type (not ntext type) and it's a 3-party program that stored a html on it. when I query it. it display sth like (not whole text)
location notes
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
¯»ÀµØ©ú§øÂ§©ú¼Ó <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<STYLE type=text/css> P, UL, OL, DL, DIR, MENU, PRE { margin: 0 auto;}</STYLE>

<META content="MSHTML 6.00.2900.2180" name=GENERATOR></HEAD>
<BODY leftMargin=1 topMargin=1 rig

wow, it displays chinese again

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2005-09-01 : 04:57:41

quote:
Originally posted by Kristen

Does

SELECT TOP 10 MyNotesColumn FROM MyTable

display Chinese? If not you are perhaps missing some installed Font

Kristen

i deleted part of the string and it confirmed it shows chinese characters correctly. However, it works for me but my fd only work when open the data but not in query analyzer. Any difference may cause this?

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2005-09-01 : 05:19:21

quote:
Originally posted by yipchunyu

quote:
Originally posted by Kristen

Does

SELECT TOP 10 MyNotesColumn FROM MyTable

display Chinese? If not you are perhaps missing some installed Font

Kristen

i deleted part of the string and it confirmed it shows chinese characters correctly. However, it works for me but my fd only work when open the data but not in query analyzer. Any difference may cause this?

I changed the datatype from text to ntext and reimport the data. It works now. However, any possible solution other than this? I don't want to change the table structure of the original database. Any idea?

Kristen
Test

22859 Posts

Posted - 2005-09-01 : 05:59:17

"Will TEXT datatype store Chinese characters"

It might. It will display the characters to the user that their system stored. So if the application captures Unicode to be stored, AND presents it as ASCII to SQL Server AND the application then retrieves the data as ASCII AND THEN displays it as Unicode THEN it might work - that's a lot of "ifs"!

But NTEXT is what you are supposed to do in these circumstances!

I reckon Query Analyser is just displaying the byte characters to you. It needs an "application" to handle the appropriate font / locale / character set and so on.

But I don't have any real working knowledge of Unicode, so I may be way off beam.

Kristen

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources