unicode manipulation - 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

SQL Server 2000 Forums

Transact-SQL (2000)

unicode manipulation

Author

Topic

mkh
Starting Member

18 Posts

Posted - 2005-05-22 : 09:45:40

I have a string which contains unicode text & number.

062F062C0627063306410637062F0628062700200633062F0641062F062C06330646062C062F0020003800360030003400390032003100390030003600300032

1> I need to extract the separate the unicode text& no.
2> As the unicode text can vary in length & the no comes after the text
3> The no is encoded in last part of the string prefixed by '003'

'003800360030003400390032003100390030003600300032'
so i need to extract 860492190602 and the unicode before that

"062F062C0627063306410637062F0628062700200633062F0641062F062C06330646062C062F0020"

Is it possible to write a single query ?

regds,
Mac.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-05-22 : 10:55:14

Well, what you have is a string of 32 Unicode characters encoded as UTF-16 BE, consisting of:

062F 062C 0627 0633 0641 0637 062F 0628 0627
nine Arabic letters: dal jeem alef seen feh tah dal beh alef
0020
one space
0633 062F 0641 062F 062C 0633 0646 062C 062F
nine Arabic letters: seen dal feh dal jeem seen noon jeem dal
0020
one space
0038 0036 0030 0034 0039 0032 0031 0039 0030 0036 0030 0032
twelve (Basic Latin) digits: 8 6 0 4 9 2 1 9 0 6 0 2

So you have three things to do:
1. Get the characters into the right type (nvarchar)
2. Decide where the digits start
3. Turn the digits into a numeric type

The biggest problem is the first part, since SQL Server stores Unicode strings as UTF-16 LE.
I'd suggest you write a function like this to swap the bytes:


CREATE FUNCTION dbo.ByteSwap(@in varbinary(8000))
RETURNS varbinary(8000)
AS
BEGIN
  DECLARE @b int, @l int, @res varbinary(8000)
  SELECT @b = 1, @res = 0x, @l = LEN(@in)
  WHILE @b <= @l BEGIN
    SET @res = @res + SUBSTRING(@in, @b+1,1) + SUBSTRING(@in, @b, 1)
    SET @b = @b + 2
  END
  RETURN @res
END

Once you have that, the rest if fairly straightforward:


DECLARE @s varbinary(64)

SET @s = 0x062F062C0627063306410637062F062806270020 +
  0x0633062F0641062F062C06330646062C062F0020 +
  0x003800360030003400390032003100390030003600300032

SELECT s, LEFT(s, first_digit - 2) AS the_words,
  CAST(SUBSTRING(s, first_digit, 64) AS bigint) AS the_number
FROM (
    SELECT s, PATINDEX('%[0-9]%', s) AS first_digit
    FROM (
        SELECT CAST(dbo.ByteSwap(@s) AS nvarchar(32)) AS s
      ) AS A
  ) AS A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources