February 16, 2010 at 10:22 pm
dr_csharp (2/16/2010)
Paul White (2/16/2010)
I hope everyone that reads this thread appreciates the limitations of that function. Yikes!i dont know what do u mean by limitation, cause this function has one goal and that is converting a field from UTF-8 to Asccii, for example convert '???' to '462' but why don't you help for more efficiency and rid limitations ?
The lack of sample data and a clear definition of what you want to achieve makes it hard to help.
Don't take my criticism of your function so personally.
Unicode/UTF-8 is not a specialism of mine, but I am happy to contribute if I can, and if you make it easier to do so.
If it would help you, I can list the aspects of the function that I consider to be 'limitations'.
Otherwise, post some sample data and a specification, and you'll get some suggestions.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 16, 2010 at 10:38 pm
Right. I think I have worked out what you are trying to do. Correct me on the following where I am wrong:
You have data stored in a database which is numeric, but written in Urdu/Persian.
The data is stored in an NVARCHAR or NCHAR field.
You require a method to convert this data from the Urdu representation to the Hindu-Arabic numerals like so:
? = 9
? = 8
? = 7
? = 6
? = 5
? = 4
? = 3
? = 2
? = 1
? = 0
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 16, 2010 at 10:49 pm
Paul White (2/16/2010)
Right. I think I have worked out what you are trying to do. Correct me on the following where I am wrong:You have data stored in a database which is numeric, but written in Urdu/Persian.
The data is stored in an NVARCHAR or NCHAR field.
You require a method to convert this data from the Urdu representation to the Hindu-Arabic numerals like so:
? = 9
? = 8
? = 7
? = 6
? = 5
? = 4
? = 3
? = 2
? = 1
? = 0
Paul
Yes Paul, data are numeric stored in NVarchar fields in persian. and the function i did, is going to convert any number to latin format.
February 16, 2010 at 11:06 pm
dr_csharp (2/16/2010)
Yes Paul, data are numeric stored in NVarchar fields in persian. and the function i did, is going to convert any number to latin format.
Any other details you'd like to share? There's nothing worse than doing a whole heap of work, only for someone to turn around and say, "Oh yeah, I probably should have mentioned..."
Some sample input data and expected output would really help a lot.
Knowing the size of the data and how it is used and how important performance is to you would also help.
Thanks
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 16, 2010 at 11:17 pm
Paul White (2/16/2010)
dr_csharp (2/16/2010)
Yes Paul, data are numeric stored in NVarchar fields in persian. and the function i did, is going to convert any number to latin format.
Any other details you'd like to share? There's nothing worse than doing a whole heap of work, only for someone to turn around and say, "Oh yeah, I probably should have mentioned..."
Some sample input data and expected output would really help a lot.
Knowing the size of the data and how it is used and how important performance is to you would also help.
Thanks
Paul
1-as you know i asked this question and no one has appropriate answer after 2 days.
2-i converted all my past data, and change this function to a 'after insert trigger' to do conversion online.
3-nothing specific for sample, just seems a record have some normal data and 3 other fields ( Comment,Vote,Click ) in Persian representation.
i appreciate if you know better way or any alter that improve performance 🙂
February 17, 2010 at 12:54 am
dr_csharp (2/16/2010)
1-as you know i asked this question and no one has appropriate answer after 2 days.
You know that if you don't get an 'appropriate answer' after 3 days, you're entitled to a full refund? :laugh:
Never mind, I'm taking a look at this now, I'll post back in a bit.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 17, 2010 at 2:17 am
dr_csharp (2/16/2010)
1-as you know i asked this question and no one has appropriate answer after 2 days.
I think the lack of detail in your original question didn't help. This is your original post (in its entirety)
dr_csharp (2/14/2010)
dear friendshow can i convert a field with nvarchar type to int?
thanks
I think you'll agree there isn't an awful lot of detail on your exact problem.
Nigel
How to post questions
February 17, 2010 at 3:35 am
Here we go then...
-- Switch to the tempdb database
USE tempdb;
GO
-- Drop any objects created by this script if they are still around from a previous run
IF OBJECT_ID(N'dbo.SampleData', N'U') IS NOT NULL DROP TABLE dbo.SampleData;
IF OBJECT_ID(N'dbo.ConvertedData', N'U') IS NOT NULL DROP TABLE dbo.ConvertedData;
IF OBJECT_ID(N'dbo.TriggerTest', N'U') IS NOT NULL DROP TABLE dbo.TriggerTest;
IF OBJECT_ID(N'dbo.ToPersian', N'IF') IS NOT NULL DROP FUNCTION dbo.ToPersian;
IF OBJECT_ID(N'dbo.FromPersian', N'IF') IS NOT NULL DROP FUNCTION dbo.FromPersian;
GO
-- Inline table-valued function (ITVF) to convert latin numbers to persian
-- SCHEMABINDING used to force the function to be evaluated for determinism
-- ITVFs are much faster than scalar functions, and can operate on sets of data
CREATE FUNCTION dbo.ToPersian (@Input AS NVARCHAR(40))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
-- Nested replace to convert each of the ten possible characters
SELECT converted =
CONVERT(NVARCHAR(40),
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
-- Use a binary collation for comparisons, for speed
@Input COLLATE LATIN1_GENERAL_BIN,
N'9', N'?'),
N'8', N'?'),
N'7', N'?'),
N'6', N'?'),
N'5', N'?'),
N'4', N'?'),
N'3', N'?'),
N'2', N'?'),
N'1', N'?'),
N'0', N'?'));
GO
-- Inline table-valued function to convert persian numbers to latin
-- SCHEMABINDING used to force the function to be evaluated for determinism
-- ITVFs are much faster than scalar functions, and can operate on sets of data
CREATE FUNCTION dbo.FromPersian (@Input NVARCHAR(40))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
-- Nested replace to convert each of the ten possible characters
SELECT converted =
CONVERT(NVARCHAR(40),
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
-- Use a binary collation for comparisons, for speed
@Input COLLATE LATIN1_GENERAL_BIN,
N'?', N'9'),
N'?', N'8'),
N'?', N'7'),
N'?', N'6'),
N'?', N'5'),
N'?', N'4'),
N'?', N'3'),
N'?', N'2'),
N'?', N'1'),
N'?', N'0'));
GO
-- Test 1: Convert a single value from latin to persian
SELECT latin_to_persian_test = converted
FROM dbo.ToPersian(N'123456789.01')
GO
-- Test 2: Convert the result of test 1 back to latin
SELECT round_trip_conversion_test = FP.converted
FROM dbo.ToPersian(N'123456789.01') TP
CROSS
APPLY dbo.FromPersian(TP.converted) FP
GO
-- Test 3: Show that the two inline functions have been checked and are deterministic and precise
SELECT function_name = N'dbo.ToPersian',
is_deterministic = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.ToPersian', N'IF'), 'IsDeterministic'),
is_precise = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.ToPersian', N'IF'), 'IsPrecise'),
is_sytem_verified = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.ToPersian', N'IF'), 'IsSystemVerified')
UNION ALL
SELECT N'dbo.FromPersian',
OBJECTPROPERTYEX(OBJECT_ID(N'dbo.FromPersian', N'IF'), 'IsDeterministic'),
OBJECTPROPERTYEX(OBJECT_ID(N'dbo.FromPersian', N'IF'), 'IsPrecise'),
OBJECTPROPERTYEX(OBJECT_ID(N'dbo.FromPersian', N'IF'), 'IsSystemVerified');
GO
-- Create a table to hold some sample data in persian format
CREATE TABLE dbo.SampleData
(
latin_format INTEGER NOT NULL,
persian_format NVARCHAR(40) NOT NULL,
);
GO
-- Create a table to hold the results of converting the sample data to latin
CREATE TABLE dbo.ConvertedData
(
converted INTEGER NOT NULL,
)
GO
-- Create a table to test using a trigger
CREATE TABLE dbo.TriggerTest
(
data NVARCHAR(40) NOT NULL,
);
GO
-- Test 4:
-- Add the numbers 1 to 32,767 to the sample data table
-- The numbers will be converted from latin to persian format
WITH Numbers
AS (
-- This generates the numbers 1 to 32,767
SELECT TOP (32767)
n = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master.sys.columns C1,
master.sys.columns C2
)
INSERT dbo.SampleData WITH (TABLOCK)
(latin_format, persian_format)
SELECT Numbers.n, CA.converted
FROM Numbers
CROSS
APPLY -- Convert latin to persian
dbo.ToPersian (n) CA;
GO
-- Show a sample of the converted data
SELECT TOP (10)
test_4 = SD.persian_format
FROM dbo.SampleData SD;
GO
-- Test 5:
-- Convert the 32,767 persian numbers in dbo.SampleData to latin
-- and store in the dbo.ConvertedData table
INSERT dbo.ConvertedData WITH (TABLOCK)
(converted)
SELECT CA.converted
FROM dbo.SampleData SD
CROSS
APPLY -- Convert persian to latin
dbo.FromPersian (SD.persian_format) CA;
GO
-- Show a sample of the converted data
SELECT TOP (10)
test_5 = CD.converted
FROM dbo.ConvertedData CD;
GO
-- Create a trigger on the dbo.TriggerTest table
-- to convert persian INSERTs to latin format
-- as part of the insert
CREATE TRIGGER trg_TriggerTest
ON dbo.TriggerTest
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON; -- Suppress x row(s) affected messages
SET ROWCOUNT 0; -- Restore ROWCOUNT
-- If no rows were INSERTed, exit now
IF NOT EXISTS (SELECT * FROM inserted) RETURN;
-- Convert the persian data to latin
-- and store the result in the table
INSERT dbo.TriggerTest
SELECT CA.converted
FROM inserted INS
CROSS
APPLY dbo.FromPersian (INS.data) CA;
END;
GO
-- Test 6:
-- Test the trigger by inserting the 32,767 rows of persian data from the dbo.SampleData table
INSERT dbo.TriggerTest WITH (TABLOCK)
(data)
SELECT persian_format
FROM dbo.SampleData;
GO
-- Show that the persian data was converted to latin by the trigger
SELECT test_6 = TT.data
FROM dbo.TriggerTest TT WITH (TABLOCK);
GO
-- Tidy up (comment this out to leave the objects in place for inspection)
IF OBJECT_ID(N'dbo.SampleData', N'U') IS NOT NULL DROP TABLE dbo.SampleData;
IF OBJECT_ID(N'dbo.ConvertedData', N'U') IS NOT NULL DROP TABLE dbo.ConvertedData;
IF OBJECT_ID(N'dbo.TriggerTest', N'U') IS NOT NULL DROP TABLE dbo.TriggerTest;
IF OBJECT_ID(N'dbo.ToPersian', N'IF') IS NOT NULL DROP FUNCTION dbo.ToPersian;
IF OBJECT_ID(N'dbo.FromPersian', N'IF') IS NOT NULL DROP FUNCTION dbo.FromPersian;
-- End of script
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 17, 2010 at 11:48 am
Nice solution Paul.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 18, 2010 at 2:10 am
CirquedeSQLeil (2/17/2010)
Nice solution Paul.
Indeed, very thorough. I think you've scared off the OP though 🙂
Nigel
How to post questions
February 18, 2010 at 3:33 am
nigel. (2/18/2010)
CirquedeSQLeil (2/17/2010)
Nice solution Paul.Indeed, very thorough. I think you've scared off the OP though 🙂
I hope not - but even if that is the case, I would hope that someone else might read it with interest.
In fact the algorithm I use in my functions is not terribly efficient in some scenarios - other solutions to this sort of character-replacement problem exist - but it is definitely better than a WHILE loop inside a scalar T-SQL function.
My personal preference would normally be to write it as a CLR function - either scalar or streaming TVF, depending on the requirement. CLR scalar functions are much quicker to call than T-SQL scalar functions, and a streaming TVF can be optimal on larger inputs sets.
The inline T-SQL function is very efficient from a plug-in to the query plan point of view, but all those nested REPLACEs would drag performance down on longer strings. Anyway, I digress.
I thought I should keep this example fairly simple - I think a whole load of C# would have scared off more than the OP! Though, judging from his forum name, he might have preferred it...?
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply