If you are Czech, Polish or Turkis speaking and using SQL Server, then you can help to improve the SQL Server Books Online translations to these languages.
On the Microsoft Language Portal Blog there is article, how you can help improve the books online.
See the article for details. Calling Czech, Polish and Turkish users of SQL Server – Microsoft Language Portal Blog – Site Home –
To write this blog post I was inspired by a question on Ask SQL Server Central.com about finding a substring between two characters.
Some times it is necessary to process input string and retrieve some parts of them. For this purposes we can write some more complex T-SQL constructs or use the CLR functionality of SQL Server 2005 and SQL Server 2008 and write some functions for processing Regular Expressions.
Generally all the string manipulations (especially on very large string values) are much better performing using a CLR scalar and table-valued functions than their equivalent using pure T-SQL code. Also using regular expression you do not need to write specialized functions for particular processing as you simply write a regular expression and use it in the RegEx function.
For general info about writing a CLR functions read MSDN.
More info on regular expressions you can find on Regular-Expressions.info and you can find inspiration also in the Regular Expression Library.
For such use you can find a below SQLRegEx class which contains two functions – RegExMatch with is a scalar CLR function to return particular group from within particular match and CLR table-valued function RegExMatches which return all matches and groups.
Below is a code snippet you can use to create an assembly.
public class SQLRegEx
{
private class RegExRow
{
/// <summary>
/// Private class for passing matches of the RegExMatches to the FillRow method
/// </summary>
/// <param name=”rowId”>ID of the Row</param>
/// <param name=”matchId”>ID of the Match</param>
/// <param name=”groupID”>ID of the Group within the Match</param>
/// <param name=”value”>Value of the particular group</param>
public RegExRow(int rowId, int matchId, int groupID, string value)
{
RowId = rowId;
MatchId = matchId;
GroupID = groupID;
Value = value;
}
public int RowId;
public int MatchId;
public int GroupID;
public string Value;
}
/// <summary>
/// Applies Regular Expression on the Source string and returns value of particular group from withing a specified match
/// </summary>
/// <param name=”sourceString”>Source string on which the regular expression should be applied</param>
/// <param name=”pattern”>Regular Expression pattern</param>
/// <param name=”matchId”>ID of the Match to be returned 1 inex-based</param>
/// <param name=”groupId”>ID of the group from within a match to return. GroupID 0 returns complete match</param>
/// <returns>Value of the Group from within a Match</returns>
[SqlFunction(IsDeterministic=true)]
public static SqlChars RegExMatch(string sourceString, string pattern, int matchId, int groupId)
{
Match m = null;
Regex r = new Regex(pattern, RegexOptions.Compiled);
if (matchId == 1)
{
m = r.Match(sourceString);
}
else if (matchId > 1)
{
MatchCollection mc = r.Matches(sourceString);
m = mc != null && mc.Count > matchId – 1 ? mc[matchId - 1] : null;
}
return m != null && m.Groups.Count > groupId ? new SqlChars(m.Groups[groupId].Value) : SqlChars.Null;
}
/// <summary>
/// Applies Regular Expression o the Source strings and return all matches and groups
/// </summary>
/// <param name=”sourceString”>Source string on which the regular expression should be applied</param>
/// <param name=”pattern”>Regular Expression pattern</param>
/// <returns>Returns list of RegExRows representing the group value</returns>
[SqlFunction(FillRowMethodName = "FillRegExRow")]
public static IEnumerable RegExMatches(string sourceString, string pattern)
{
Regex r = new Regex(pattern, RegexOptions.Compiled);
int rowId = 0;
int matchId = 0;
foreach (Match m in r.Matches(sourceString))
{
matchId++;
for (int i = 0; i < m.Groups.Count; i++)
{
yield return new RegExRow(++rowId, matchId, i, m.Groups[i].Value);
}
}
}
/// <summary>
/// FillRow method to populate the output table
/// </summary>
/// <param name=”obj”>RegExRow passed as object</param>
/// <param name=”rowId”>ID or the returned row</param>
/// <param name=”matchId”>ID of returned Match</param>
/// <param name=”groupID”>ID of group in the Match</param>
/// <param name=”value”>Value of the Group</param>
public static void FillRegExRow(Object obj, out int rowId, out int matchId, out int groupID, out SqlChars value)
{
RegExRow r = (RegExRow)obj;
rowId = r.RowId;
matchId = r.MatchId;
groupID = r.GroupID;
value = new SqlChars(r.Value);
}
}
This is a scalar function which takes three arguments. First is the source string to be parsed using regular expressions. Second parameter is regular expression itself, third is a matchId (counted from 1 and which represents a match to be returned) to be returned if there could be more matches and fourth is a Group ID to be returned from within the regular expression match. When there is no group or you want to return simply the complete Match, pass 0.
This is CLR table function and it returns all matches including each group in each match. The complete Match has groupID equal to 0 in the output. Input parameters are source string to be parsed using the regular expression a second is the regular expression pattern.
Once we have a compiled assembly, we can register the assembly and function in database and start using them for various Regular Expressions processing.
CREATE ASSEMBLY [SQLRegEx]
AUTHORIZATION [dbo]
FROM 'C:CLRSQLRegEx.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION [dbo].[fn_RegExMatch](
@sourceString nvarchar(max), --Source string to be processed by regular expression
@pattern nvarchar(4000), --Regular expression (pattern) to be applied on the source string
@matchId int, --ID of the match to be returned (if there are more matches, you can return a concrete one). Numbering starts from 1
@groupID int --ID of the group in the match to be returned. GroupID = 0 represents a complete match.
)
RETURNS nvarchar(4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLRegEx].[SQLRegEx].[RegExMatch]
GO
CREATE FUNCTION [dbo].[fn_RegExMatches](
@sourceString nvarchar(max), --Source string to be processed by regular expression
@pattern nvarchar(4000) --Regular expression (pattern) to be applied on the source string
)
RETURNS TABLE (
[rowId] int, --RowId each row as it`s ID
[matchId] int, --ID of particular match (starts from 1)
[groupId] int, --ID of particular group in RegEx match (GroupID = 0) represents a complete match
[value] nvarchar(4000) --value of the group
) WITH EXECUTE AS CALLER
EXTERNAL NAME [SQLRegEx].[SQLRegEx].[RegExMatches]
GO
Eg. Split semicolon separated list of Key=Value pairs. Pair has the same matchId and Key has groupID = 1 and value has groupID=2. Without the WHERE Condition also the GroupID 0 will be returned which contains each pair.
SELECT
*
FROM dbo.fn_RegExMatches('Key1=Val1;Key2=Val2;Key3=Val3;Key4=Val4', 's*(w+)s*=s*?(.*?)(?=;|z)')
WHERE groupId > 0
rowId matchId groupId value ———– ———– ———– —— 2 1 1 Key1 3 1 2 Val1 5 2 1 Key2 6 2 2 Val2 8 3 1 Key3 9 3 2 Val3 11 4 1 Key4
SELECT
*
FROM dbo.fn_RegExMatches('1,2,3,4,5,6,7,8', '(d+?)(?:,|z)')
WHERE groupId = 1
--or even this and doesn’t matter what separator is used
SELECT
*
FROM dbo.fn_RegExMatches('1,2,3,4,5;6,7,8', 'w+')
SELECT
*
FROM dbo.fn_RegExMatches('This is a sample sentence to Split.', 'w+')
SELECT dbo.fn_RegExMatch('Key = Value' ,'s*w+s*=s*(.*)', 1, 1)
SELECT dbo.fn_RegExMatch('This is a sample sentence to Split.', 'w+', 4, 0)
SELECT dbo.fn_RegExMatch('1,2,3,4,5;6,7,8', 'w+', 6, 0)
--validating email address
DECLARE @emailAddress nvarchar(20)
SET @emailAddress = 'name@domain.com'
SELECT
CASE
WHEN
@emailAddress =
dbo.fn_RegExMatch(@emailAddress, '([a-zA-Z0-9_-.]+)@(([[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.)|(([a-zA-Z0-9-]+.)+))([a-zA-Z]{2,4}|[0-9]{1,3})', 1, 0)
THEN 1
ELSE 0
END AS ValidEmail
--lists all invalid email
WITH emails AS (
SELECT 'name@domain.com' AS email UNION ALL
SELECT 'name@subdomain.domain.com' UNION All
SELECT 'name@this.email.is.wrong' UNION ALL
SELECT 'another.wrong@@domain.com'
)
SELECT
*
FROM emails
WHERE email <> dbo.fn_RegExMatch(email, '([a-zA-Z0-9_-.]+)@(([[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.)|(([a-zA-Z0-9-]+.)+))([a-zA-Z]{2,4}|[0-9]{1,3})', 1, 0)
WITH StringDates AS (
SELECT '2010/09/3' AS StringDate UNION ALL
SELECT '2010/07/09' UNION ALL
SELECT '2009/1/15'
)
SELECT
StringDate,
dbo.fn_RegExMatch(StringDate, '(d{4})/(d{1,2})/(d{1,2})', 1, 3) AS DateDay
FROM StringDates
Hope, this article will help you using regular expressions in SQL Server and you will be able to simplify and speed-up your queries.
You may be also interested in my next post SQL Server Regular Expressions – Replacements which describes possibilities of string replacements using regular expressions.
A lot of time we need truncate (trim) a datetime value.
E.g.. we use GETDATE() function, but we need only the Date portion of the value and not the time. So we need to set the time to the midnight 00:00:00.000.
There are several possibilities to to this and and mostly I see using the CONVERT function with the style parameter converting to varchar and than back to datetime.
CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 112))
Although it is possible to use this syntax, it is not the best solution.
Much better is to use DATEADD and DATEDIFF functions.
DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
This counts a difference in days from zero and than it adds it back to zero, so we receive the value trimmed to whole days.
By simple modification we can adjust this to the first of month, last of month, whole hours, minutes etc. by simply changing the datepart.
--First of Month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) --Last of Month: DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0) - 1 --First of Year: DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0) --Last of Year: DATEADD(year, DATEDIFF(year, 0, GETDATE()) + 1, 0) – 1 --Hour: DATEADD(hour, DATEDIFF(hour, 0, GETDATE()) + 1, 0) – 1 --Minute: DATEADD(minute, DATEDIFF(minute, 0, GETDATE()) + 1, 0) – 1
Not only by this format is easier to achieve needed results but it’s even around two times faster!
We can compare the speed by simple speed test script:
DECLARE @startTime datetime
DECLARE @truncDate datetime
DECLARE @cnt int
DECLARE @msg varchar(400)
DATEADD and DATEDIFF
SELECT
@cnt = 0,
@startTime= GETDATE()
WHILE @cnt < 1000000
SELECT
@truncDate = CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 112)),
@cnt = @cnt + 1
SELECT
@msg = 'Trucation using CONVERT: resutl = ' + CONVERT(varchar(30), @truncDate) +
' : Took: ' + CONVERT(varchar, DATEDIFF(MILLISECOND, @startTime, GETDATE())) + ' ms'
PRINT @msg
SELECT
@cnt = 0,
@startTime= GETDATE()
WHILE @cnt < 1000000
SELECT
@truncDate = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0),
@cnt = @cnt + 1
SELECT
@msg = 'Trucation using DATEDIFF & DATEADD: resutl = ' + CONVERT(varchar(30), @truncDate) +
' : Took: ' + CONVERT(varchar, DATEDIFF(MILLISECOND, @startTime, GETDATE())) + ' ms'
PRINT @msg
If you delete a job which handles particular SSRS schedule by mistake, then it can happen, that you will be unable to delete a report schedule.
You delete the schedule and when you refresh the management the schedule you wanted to delete appears again.
This is caused by a trigger [dbo].[Schedule_DeleteAgentJob] trigger on the [dbo].[Schedule] table in the report server database, which doesn’t check for the job if it exists before it tries to delete it. And therefore if you deleted the job by mistake, then there is raised an exception in the trigger and whole transaction with deletion of schedule is rolled back.
You can simply modify the trigger by adding additional check for the existence of the job. And once the modification is applied, your SSRS schedules will work even you delete the job for that schedule manually.
ALTER TRIGGER [dbo].[Schedule_DeleteAgentJob] ON [dbo].[Schedule]
AFTER DELETE
AS
DECLARE id_cursor CURSOR
FOR
SELECT ScheduleID from deleted
OPEN id_cursor
DECLARE @next_id uniqueidentifier
FETCH NEXT FROM id_cursor INTO @next_id
WHILE (@@FETCH_STATUS <> -1) -- -1 == FETCH statement failed or the row was beyond the result set.
BEGIN
if (@@FETCH_STATUS <> -2) -- - 2 == Row fetched is missing.
BEGIN -- Check that job exists before trying to delete it.
IF (EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name = CONVERT(nvarchar(128), @next_id)))
exec msdb.dbo.sp_delete_job @job_name = @next_id -- delete the schedule
END
FETCH NEXT FROM id_cursor INTO @next_id
END
CLOSE id_cursor
Both SSRS 2005 and SSRS 2008 have the same problem.
I’m mentioning this also in my previous blog item about scale-out deployment of SSRS on Standard edition of SQL Server.
SQL Server 2005 reporting services support scale-out deployment in Enterprise version. Unfortunately the scale-out deployment is not supported in Standard version of SQL Server 2005.
Some time ago I received a task to make automatic failover solution on reporting services across 3 SQL server reporting services instances on 3 different machines. There was setup a load balancing for that machines and it was necessary do create a solution when all 3 instances are fully synchronized and if some of the servers fails, user cannot be affected by this situation. (User must be able to run their reports and must receive all reports to which they subscribed. Also it is necessary when users create some reports, they must be automatically deployed across all 3 nodes).
When you run 2 or more instances of the SSRS standard version against one SSRS database which is normal in the enterprise version, you will receive a message, that the scale-out deployment is not supported.
So you can make a redundancy making 3 separate instances with 3 separate databases. Unfortunately this solution is hard to maintain in situation when you have hundreds of different reports on the server and new one are often created and some modified. Also it is hard to maintain subscriptions for the reports.
I started about solution for that situation and was investigating for some time.
Finally I’ve played with Replication which is supported by the standard version and it works. I’ve configured a fully automatic fail-over solution using the standard version of SSRS, which also correctly handles user subscriptions.
The steps for successful setup of scale-out deployment using the standard version you need to setup the primary node first and then all the secondary nodes. The term primary node here means the one which database will be published for the other (secondary) nodes. It is also necessary that all nodes runs the same version of SQL Server Reporting services including the same service packs and patches. Also this assumes, that the report server databases will be running on the same instance of SQL Server (or Cluster).
So we install and setup first node of the SSRS services. As we will be using more nodes, we have to backup encryption keys of the primary SSRS instance as it will have to be used also by all other instances.
After the primary instance is fully setup, we will create a Merge publication for the primary database. We will publish only tables and will publish all tables except the [Keys], [Events], [Notifications] tables. We publish only the report server database, not the temporary database.
We need to exclude the Keys table, because each instance has it’s own key stored in the table. Event we later restore the encryption key and it will be the same among all instances, each instance has it’s own name and creates a new entry in the Keys table. And when there is more than one entry in the table, it is automatically detected as scale-out deployment, which is supported only by the Enterprise version.
The Events and Notifications tables are used for monitoring of Subscriptions and events in SSRS. To support correct subscriptions processing we cannot replicate those tables.
The Merge replication is necessary as users are automatically directed to the nodes by load balances and we need automatically update all 3 nodes whenever there is any change on any node.
Once the primary node is set-up, we can setup all secondary nodes and the steps will be the same for each additional node.
So we install and setup the secondary node as normal standalone installation of SSRS. Once the node is set-up and configured, we restore encryption key of the primary SSRS instance to be able to share encrypted data among all instances.
After the encryption key is restored, we will setup replication for the database of the secondary node. Then we are ready to subscribe to the publication of the primary node database. You can run the replication agent continuously or schedule it depending how quickly you need the changes among nodes to be synchronized. I suggest also to run the subscription under Server subscription type with each node set up to other priority, so you have automatic conflict resolution when multiple users eventually changes the same report at the same time.
First we need to update [dbo].[Schedule_DeleteAgentJob] trigger on the [dbo].[Schedule]. This update we do on the Primary node database and will be automatically replicated to the secondary nodes. It is necessary, because there is a bug in the trigger (event in SSRS 2008). For each subscription in SSRS there is a job created. And if you delete the job first, then you are unable to delete the subscription at all as the trigger fails and the delete subscription is rolled back. The problem is, that the existence of the job is not checked in the trigger before deletion. And we need this check, because when the table is replicated then the trigger will try to delete the job again and will fail.
ALTER TRIGGER [dbo].[Schedule_DeleteAgentJob] ON [dbo].[Schedule]
AFTER DELETE
AS
DECLARE id_cursor CURSOR
FOR
SELECT ScheduleID from deleted
OPEN id_cursor
DECLARE @next_id uniqueidentifier
FETCH NEXT FROM id_cursor INTO @next_id
WHILE (@@FETCH_STATUS <> -1) -- -1 == FETCH statement failed or the row was beyond the result set.
BEGIN
if (@@FETCH_STATUS <> -2) -- - 2 == Row fetched is missing.
BEGIN -- Check that job exists before trying to delete it.
IF (EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name = CONVERT(nvarchar(128), @next_id)))
exec msdb.dbo.sp_delete_job @job_name = @next_id -- delete the schedule
END
FETCH NEXT FROM id_cursor INTO @next_id
END
CLOSE id_cursor
After update of the trigger we need some metadata changes on all secondary databases. We need to drop tables [dbo].[Event] and [dbo].[Notifications] and Create instead of the aliases to the primary server databases. It is necessary so there is correct processing of the subscriptions and other events. IF we do not make this change, it will often happen, that the event will be processed by more than one node, you will receive exceptions or receive subscribed reports more than once or the processing never ends. By this change it is ensured, that only on node processes the event, as when a node is taking event it puts lock on the tables and no other nodes can process the event.
-- Drop Event Table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Event]') AND type in (N'U'))
DROP TABLE [dbo].[Event]
GO
-- Create Alias for the Event table in the Publisher database
CREATE SYNONYM [dbo].[Event]
FOR [PrimaryReportServerDB].[dbo].[Event]
GO
-- Drop the Notifications table
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Notifications_Subscriptions]') AND parent_object_id = OBJECT_ID(N'[dbo].[Notifications]'))
ALTER TABLE [dbo].[Notifications]
DROP CONSTRAINT [FK_Notifications_Subscriptions]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Notifications]') AND type in (N'U'))
DROP TABLE [dbo].[Notifications]
GO
-- Create Alias for the Notifications table in the Publisher database
CREATE SYNONYM [dbo].[Notifications]
FOR [PrimaryReportServerDB].[dbo].[Notifications]
GO
After this modifications you have fully working Load balancing and fail over of SSRS under SQL Server 2005 standard.
It is essential to have the same key on all nodes to be able to read the data among nodes. Also as mentioned it is essential not to replicate the Keys table. As each node adds its key under its instance and machine name into the table and it is used for scale out deployment of Enterprise edition of SQL server. Unfortunately when standard edition finds more keys in the table it shows error messages about unsupported scale out deployment.
If this eventually on some or all nodes happen it is necessary to delete all records from the table except the record with value -1 in the Client column. Then it is necessary to restore the encryption key again to be able to access encrypted data.
I have to read data from AS400 machine using an ODBC driver under SQL Server 2005. The data on the AS400 machine are encoded using EBCDIC 870 – Multilingual/ROECE (Latin-2) code page and contains Czech diacritics.
Unfortunately,SQL Server 2005 have no native support for EBCDIC 870 code page and when you read the data, you receive wrong characters.
There are some EBCDIC character sets supported by SQL server, but not the 870 one.
select
*
from fn_helpcollations()
I was googling and binging for some time, but didn’t find any suitable solution, if you need to select live data from the AS400 machine.
Finally I was looking deeper into to the problem and found an acceptable work around.
The ODBC driver and AS400 support HEX() function, which returns sequence of hexadecimal codes representing the value.
SELECT
*
FROM OPENQUERY(AS400, 'SELECT HEX(FieldName) AS FieldName FROM AS400Table')
You can crate an CLR function which will allow you to convert the source CBCDIC 870 code page to the destination code page of you choice.
[SqlFunction()]
public static SqlString fnEBCDIC870ToSql(SqlString hexBytes, int destinationCodePage)
{
if (hexBytes.IsNull) return hexBytes;
string str = hexBytes.ToString();
byte[] buffer = new byte[str.Length / 2];
Encoding destinationEncoding = Encoding.GetEncoding(destinationCodePage);
for (int i = 0; i < buffer.Length; i++)
{
buffer[i] = byte.Parse(str.Substring(i * 2, 2), NumberStyles.HexNumber);
}
buffer = Encoding.Convert(Encoding.GetEncoding(870), destinationEncoding, buffer);
str = destinationEncoding.GetString(buffer);
return new SqlString(str);
}
If you cannot use the CLR function on the SQL server or Database, you can simply create an Conversion table and conversion scalar function for this conversion.
for conversion to code page 1250:
CREATE FUNCTION [dbo].[fnEBCDIC870ToWin1250] (
@pHex varchar(8000)
)
RETURNS varchar(4000)
AS
BEGIN
DECLARE @result varchar(4000)
SET @result = '';
WITH Tally AS (
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) As RowNo
FROM sys.all_columns
)
SELECT
@result = @result + ISNULL(C.Character, '?')
FROM Tally
LEFT JOIN CodePage870 C ON UPPER(SUBSTRING(@pHex, (RowNo - 1) * 2 + 1, 2)) = C.HexByte
WHERE RowNo <= LEN(@pHex) / 2
ORDER BY RowNo
RETURN @result
END
The function uses a conversion table CodePage870.
CREATE TABLE CodePage870 (
HexByte char(2) NOT NULL,
Character char(1) NOT NULL,
CONSTRAINT PK_CodePage870 PRIMARY KEY (HexByte)
)
INSERT CodePage870 (
HexByte,
Character
)
SELECT '40' As HexByte, ' ' As Character UNION ALL
SELECT '41' As HexByte, ' ' As Character UNION ALL
SELECT '42' As HexByte, 'â' As Character UNION ALL
SELECT '43' As HexByte, 'ä' As Character UNION ALL
SELECT '44' As HexByte, 'ţ' As Character UNION ALL
SELECT '45' As HexByte, 'á' As Character UNION ALL
SELECT '46' As HexByte, 'ă' As Character UNION ALL
SELECT '47' As HexByte, 'č' As Character UNION ALL
SELECT '48' As HexByte, 'ç' As Character UNION ALL
SELECT '49' As HexByte, 'ć' As Character UNION ALL
SELECT '4A' As HexByte, '[' As Character UNION ALL
SELECT '4B' As HexByte, '.' As Character UNION ALL
SELECT '4C' As HexByte, '<' As Character UNION ALL
SELECT '4D' As HexByte, '(' As Character UNION ALL
SELECT '4E' As HexByte, '+' As Character UNION ALL
SELECT '4F' As HexByte, '!' As Character UNION ALL
SELECT '50' As HexByte, '&' As Character UNION ALL
SELECT '51' As HexByte, 'é' As Character UNION ALL
SELECT '52' As HexByte, 'ę' As Character UNION ALL
SELECT '53' As HexByte, 'ë' As Character UNION ALL
SELECT '54' As HexByte, 'ů' As Character UNION ALL
SELECT '55' As HexByte, 'í' As Character UNION ALL
SELECT '56' As HexByte, 'î' As Character UNION ALL
SELECT '57' As HexByte, 'ľ' As Character UNION ALL
SELECT '58' As HexByte, 'ĺ' As Character UNION ALL
SELECT '59' As HexByte, 'ß' As Character UNION ALL
SELECT '5A' As HexByte, ']' As Character UNION ALL
SELECT '5B' As HexByte, '$' As Character UNION ALL
SELECT '5C' As HexByte, '*' As Character UNION ALL
SELECT '5D' As HexByte, ')' As Character UNION ALL
SELECT '5E' As HexByte, ';' As Character UNION ALL
SELECT '5F' As HexByte, '^' As Character UNION ALL
SELECT '60' As HexByte, '-' As Character UNION ALL
SELECT '61' As HexByte, '/' As Character UNION ALL
SELECT '62' As HexByte, 'Â' As Character UNION ALL
SELECT '63' As HexByte, 'Ä' As Character UNION ALL
SELECT '64' As HexByte, '˝' As Character UNION ALL
SELECT '65' As HexByte, 'Á' As Character UNION ALL
SELECT '66' As HexByte, 'Ă' As Character UNION ALL
SELECT '67' As HexByte, 'Č' As Character UNION ALL
SELECT '68' As HexByte, 'Ç' As Character UNION ALL
SELECT '69' As HexByte, 'Ć' As Character UNION ALL
SELECT '6A' As HexByte, '|' As Character UNION ALL
SELECT '6B' As HexByte, ',' As Character UNION ALL
SELECT '6C' As HexByte, '%' As Character UNION ALL
SELECT '6D' As HexByte, '_' As Character UNION ALL
SELECT '6E' As HexByte, '>' As Character UNION ALL
SELECT '6F' As HexByte, '?' As Character UNION ALL
SELECT '70' As HexByte, 'ˇ' As Character UNION ALL
SELECT '71' As HexByte, 'É' As Character UNION ALL
SELECT '72' As HexByte, 'Ę' As Character UNION ALL
SELECT '73' As HexByte, 'Ë' As Character UNION ALL
SELECT '74' As HexByte, 'Ů' As Character UNION ALL
SELECT '75' As HexByte, 'Í' As Character UNION ALL
SELECT '76' As HexByte, 'Î' As Character UNION ALL
SELECT '77' As HexByte, 'Ľ' As Character UNION ALL
SELECT '78' As HexByte, 'Ĺ' As Character UNION ALL
SELECT '79' As HexByte, '`' As Character UNION ALL
SELECT '7A' As HexByte, ':' As Character UNION ALL
SELECT '7B' As HexByte, '#' As Character UNION ALL
SELECT '7C' As HexByte, '@' As Character UNION ALL
SELECT '7D' As HexByte, '''' As Character UNION ALL
SELECT '7E' As HexByte, '=' As Character UNION ALL
SELECT '7F' As HexByte, '"' As Character UNION ALL
SELECT '80' As HexByte, '˘' As Character UNION ALL
SELECT '81' As HexByte, 'a' As Character UNION ALL
SELECT '82' As HexByte, 'b' As Character UNION ALL
SELECT '83' As HexByte, 'c' As Character UNION ALL
SELECT '84' As HexByte, 'd' As Character UNION ALL
SELECT '85' As HexByte, 'e' As Character UNION ALL
SELECT '86' As HexByte, 'f' As Character UNION ALL
SELECT '87' As HexByte, 'g' As Character UNION ALL
SELECT '88' As HexByte, 'h' As Character UNION ALL
SELECT '89' As HexByte, 'i' As Character UNION ALL
SELECT '8A' As HexByte, 'ś' As Character UNION ALL
SELECT '8B' As HexByte, 'ň' As Character UNION ALL
SELECT '8C' As HexByte, 'đ' As Character UNION ALL
SELECT '8D' As HexByte, 'ý' As Character UNION ALL
SELECT '8E' As HexByte, 'ř' As Character UNION ALL
SELECT '8F' As HexByte, 'ş' As Character UNION ALL
SELECT '90' As HexByte, '°' As Character UNION ALL
SELECT '91' As HexByte, 'j' As Character UNION ALL
SELECT '92' As HexByte, 'k' As Character UNION ALL
SELECT '93' As HexByte, 'l' As Character UNION ALL
SELECT '94' As HexByte, 'm' As Character UNION ALL
SELECT '95' As HexByte, 'n' As Character UNION ALL
SELECT '96' As HexByte, 'o' As Character UNION ALL
SELECT '97' As HexByte, 'p' As Character UNION ALL
SELECT '98' As HexByte, 'q' As Character UNION ALL
SELECT '99' As HexByte, 'r' As Character UNION ALL
SELECT '9A' As HexByte, 'ł' As Character UNION ALL
SELECT '9B' As HexByte, 'ń' As Character UNION ALL
SELECT '9C' As HexByte, 'š' As Character UNION ALL
SELECT '9D' As HexByte, '¸' As Character UNION ALL
SELECT '9E' As HexByte, '˛' As Character UNION ALL
SELECT '9F' As HexByte, '¤' As Character UNION ALL
SELECT 'A0' As HexByte, 'ą' As Character UNION ALL
SELECT 'A1' As HexByte, '~' As Character UNION ALL
SELECT 'A2' As HexByte, 's' As Character UNION ALL
SELECT 'A3' As HexByte, 't' As Character UNION ALL
SELECT 'A4' As HexByte, 'u' As Character UNION ALL
SELECT 'A5' As HexByte, 'v' As Character UNION ALL
SELECT 'A6' As HexByte, 'w' As Character UNION ALL
SELECT 'A7' As HexByte, 'x' As Character UNION ALL
SELECT 'A8' As HexByte, 'y' As Character UNION ALL
SELECT 'A9' As HexByte, 'z' As Character UNION ALL
SELECT 'AA' As HexByte, 'Ś' As Character UNION ALL
SELECT 'AB' As HexByte, 'Ň' As Character UNION ALL
SELECT 'AC' As HexByte, 'Đ' As Character UNION ALL
SELECT 'AD' As HexByte, 'Ý' As Character UNION ALL
SELECT 'AE' As HexByte, 'Ř' As Character UNION ALL
SELECT 'AF' As HexByte, 'Ş' As Character UNION ALL
SELECT 'B0' As HexByte, '˙' As Character UNION ALL
SELECT 'B1' As HexByte, 'Ą' As Character UNION ALL
SELECT 'B2' As HexByte, 'ż' As Character UNION ALL
SELECT 'B3' As HexByte, 'Ţ' As Character UNION ALL
SELECT 'B4' As HexByte, 'Ż' As Character UNION ALL
SELECT 'B5' As HexByte, '§' As Character UNION ALL
SELECT 'B6' As HexByte, 'ž' As Character UNION ALL
SELECT 'B7' As HexByte, 'ź' As Character UNION ALL
SELECT 'B8' As HexByte, 'Ž' As Character UNION ALL
SELECT 'B9' As HexByte, 'Ź' As Character UNION ALL
SELECT 'BA' As HexByte, 'Ł' As Character UNION ALL
SELECT 'BB' As HexByte, 'Ń' As Character UNION ALL
SELECT 'BC' As HexByte, 'Š' As Character UNION ALL
SELECT 'BD' As HexByte, '¨' As Character UNION ALL
SELECT 'BE' As HexByte, '´' As Character UNION ALL
SELECT 'BF' As HexByte, '×' As Character UNION ALL
SELECT 'C0' As HexByte, '{' As Character UNION ALL
SELECT 'C1' As HexByte, 'A' As Character UNION ALL
SELECT 'C2' As HexByte, 'B' As Character UNION ALL
SELECT 'C3' As HexByte, 'C' As Character UNION ALL
SELECT 'C4' As HexByte, 'D' As Character UNION ALL
SELECT 'C5' As HexByte, 'E' As Character UNION ALL
SELECT 'C6' As HexByte, 'F' As Character UNION ALL
SELECT 'C7' As HexByte, 'G' As Character UNION ALL
SELECT 'C8' As HexByte, 'H' As Character UNION ALL
SELECT 'C9' As HexByte, 'I' As Character UNION ALL
SELECT 'CA' As HexByte, '' As Character UNION ALL
SELECT 'CB' As HexByte, 'ô' As Character UNION ALL
SELECT 'CC' As HexByte, 'ö' As Character UNION ALL
SELECT 'CD' As HexByte, 'ŕ' As Character UNION ALL
SELECT 'CE' As HexByte, 'ó' As Character UNION ALL
SELECT 'CF' As HexByte, 'ő' As Character UNION ALL
SELECT 'D0' As HexByte, '}' As Character UNION ALL
SELECT 'D1' As HexByte, 'J' As Character UNION ALL
SELECT 'D2' As HexByte, 'K' As Character UNION ALL
SELECT 'D3' As HexByte, 'L' As Character UNION ALL
SELECT 'D4' As HexByte, 'M' As Character UNION ALL
SELECT 'D5' As HexByte, 'N' As Character UNION ALL
SELECT 'D6' As HexByte, 'O' As Character UNION ALL
SELECT 'D7' As HexByte, 'P' As Character UNION ALL
SELECT 'D8' As HexByte, 'Q' As Character UNION ALL
SELECT 'D9' As HexByte, 'R' As Character UNION ALL
SELECT 'DA' As HexByte, 'Ě' As Character UNION ALL
SELECT 'DB' As HexByte, 'ű' As Character UNION ALL
SELECT 'DC' As HexByte, 'ü' As Character UNION ALL
SELECT 'DD' As HexByte, 'ť' As Character UNION ALL
SELECT 'DE' As HexByte, 'ú' As Character UNION ALL
SELECT 'DF' As HexByte, 'ě' As Character UNION ALL
SELECT 'E0' As HexByte, '' As Character UNION ALL
SELECT 'E1' As HexByte, '÷' As Character UNION ALL
SELECT 'E2' As HexByte, 'S' As Character UNION ALL
SELECT 'E3' As HexByte, 'T' As Character UNION ALL
SELECT 'E4' As HexByte, 'U' As Character UNION ALL
SELECT 'E5' As HexByte, 'V' As Character UNION ALL
SELECT 'E6' As HexByte, 'W' As Character UNION ALL
SELECT 'E7' As HexByte, 'X' As Character UNION ALL
SELECT 'E8' As HexByte, 'Y' As Character UNION ALL
SELECT 'E9' As HexByte, 'Z' As Character UNION ALL
SELECT 'EA' As HexByte, 'ď' As Character UNION ALL
SELECT 'EB' As HexByte, 'Ô' As Character UNION ALL
SELECT 'EC' As HexByte, 'Ö' As Character UNION ALL
SELECT 'ED' As HexByte, 'Ŕ' As Character UNION ALL
SELECT 'EE' As HexByte, 'Ó' As Character UNION ALL
SELECT 'EF' As HexByte, 'Ő' As Character UNION ALL
SELECT 'F0' As HexByte, '0' As Character UNION ALL
SELECT 'F1' As HexByte, '1' As Character UNION ALL
SELECT 'F2' As HexByte, '2' As Character UNION ALL
SELECT 'F3' As HexByte, '3' As Character UNION ALL
SELECT 'F4' As HexByte, '4' As Character UNION ALL
SELECT 'F5' As HexByte, '5' As Character UNION ALL
SELECT 'F6' As HexByte, '6' As Character UNION ALL
SELECT 'F7' As HexByte, '7' As Character UNION ALL
SELECT 'F8' As HexByte, '8' As Character UNION ALL
SELECT 'F9' As HexByte, '9' As Character UNION ALL
SELECT 'FA' As HexByte, 'Ď' As Character UNION ALL
SELECT 'FB' As HexByte, 'Ű' As Character UNION ALL
SELECT 'FC' As HexByte, 'Ü' As Character UNION ALL
SELECT 'FD' As HexByte, 'Ť' As Character UNION ALL
SELECT 'FE' As HexByte, 'Ú' As Character UNION ALL
SELECT 'FF' As HexByte, '?' As Character
The Script was generated by simple C# method.
public static void CreateEBCDICScript(string destinationFileName, int destinationCodePage)
{
StringBuilder sb = new StringBuilder();
byte[] buffer = new byte[1];
Encoding ebcdic = Encoding.GetEncoding(870);
Encoding destinationEncoding = Encoding.GetEncoding(destinationCodePage);
for (int i = 64; i < 256; i++)
{
buffer[0] = (byte)i;
buffer = Encoding.Convert(ebcdic, destinationEncoding, buffer);
string target = destinationEncoding.GetString(buffer);
sb.AppendFormat("SELECT '{0:X}' AS HexByte, '{1}' AS Character UNION ALL", i, target.Equals("'") ? "''" : target);
sb.AppendLine();
}
File.WriteAllText(destinationFileName, sb.ToString();
}
Finally as you have the conversion functions in database you can use below selects to receive correctly encoded strings from within the AS400 machine.
--CLR Version
SELECT
fnECDIC870ToSql(FieldName, 1250) As FieldName
FROM OPENQUERY(AS400, 'SELECT HEX(FieldName) AS FieldName FROM AS400Table')
--T-SQL Version
SELECT
fnECDIC870ToWin1250(FieldName) As FieldName
FROM OPENQUERY(AS400, 'SELECT HEX(FieldName) AS FieldName FROM AS400Table')
Pavel's SQL Pages 