بهترین ها همه در یک جا
بهترین ها همه در یک جا
برای شمسی کردن تاریخ و نمایش به صورت عددی مثلا 14001005 به صورت زیر عمل میکنیم :
ابتدا باید یک اسمبلی به دیتابیس شما اضافه بشه که کد اون به این صورت هست
CREATE ASSEMBLY [PersianSQLFunctions]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300FFE59C520000000000000000E00002210B010B00001000000008000000000000FE2E0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000AC2E00004F000000004000000804000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000040F0000002000000010000000020000000000000000000000000000200000602E7273726300000008040000004000000006000000120000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001800000000000000000000000000004000004200000000000000000000000000000000E02E0000000000004800000002000500942500001809000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B300500850100000100001172010000700A730E00000A0B07026F0F00000A0C07026F1000000A0D07026F1100000A130407026F1200000A130507026F1300000A130607026F1400000A1307032C741D8D14000001130911091602162804000006A21109177203000070A21109181205281500000A181F306F1600000AA21109197207000070A211091A1206281500000A181F306F1600000AA211091B7207000070A211091C1207281500000A181F306F1600000AA21109281700000A0A38B20000001F0B8D14000001130A110A161202281500000A1A1F306F1600000AA2110A17720B000070A2110A181203281500000A181F306F1600000AA2110A19720B000070A2110A1A1204281500000A181F306F1600000AA2110A1B7203000070A2110A1C1205281500000A181F306F1600000AA2110A1D7207000070A2110A1E1206281500000A181F306F1600000AA2110A1F097207000070A2110A1F0A1207281500000A181F306F1600000AA2110A281700000A0A06731800000A1308DE0F26720F000070281900000A1308DE0011082A000000411C0000000000000000000073010000730100000F000000160000011B300500A90000000200001172010000700A730E00000A0B07026F0F00000A0C07026F1000000A0D07026F1100000A1304032C0A021628040000060A2B5B1B8D1400000113061106161202281500000A1A1F306F1600000AA2110617720B000070A21106181203281500000A181F306F1600000AA2110619720B000070A211061A1204281500000A181F306F1600000AA21106281700000A0A06731800000A1305DE0F26720F000070281900000A1305DE0011052A00000001100000000000009797000F160000011B3005007100000003000011160A730E00000A0B07026F0F00000A0C07026F1000000A0D07026F1100000A13041202281500000A1A1F306F1600000A1203281500000A181F306F1600000A1204281500000A181F306F1600000A281A00000A281B00000A0A06731C00000A1305DE0B2616281D00000A1305DE0011052A00000001100000000000006363000B16000001133005002802000004000011730E00000A0A1D8D14000001130B110B167213000070A2110B17721D000070A2110B18722B000070A2110B197239000070A2110B1A7249000070A2110B1B725B000070A2110B1C726B000070A2110B0B1F0C8D14000001130C110C167275000070A2110C177285000070A2110C187297000070A2110C1972A3000070A2110C1A72AB000070A2110C1B72B7000070A2110C1C72C5000070A2110C1D72CD000070A2110C1E72D7000070A2110C1F0972DF000070A2110C1F0A72E5000070A2110C1F0B72EF000070A2110C0C160D0213041204281E00000A13051105130D110D4507000000060000000A0000000E00000012000000160000001A000000020000002B1A160D2B16170D2B12180D2B0E190D2B0A1A0D2B061B0D2B021C0D0611046F1100000A13060611046F1000000A13070611046F0F00000A1308032D521D8D01000001130E110E1607099AA2110E177203000070A2110E1811068C15000001A2110E197203000070A2110E1A08110717599AA2110E1B7203000070A2110E1C11088C15000001A2110E281F00000A2A720100007013097201000070130A110717321B11071F093015720F0000701207281500000A282000000A13092B091207281500000A1309110617321B11061F093015720F0000701206281500000A282000000A130A2B091206281500000A130A1B8D01000001130F110F1611088C15000001A2110F17720B000070A2110F181109A2110F19720B000070A2110F1A110AA2110F281F00000A2A1E02282100000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000BC020000237E0000280300009403000023537472696E677300000000BC060000FC00000023555300B8070000100000002347554944000000C80700005001000023426C6F620000000000000002000001471502000900000000FA2533001600000100000017000000020000000500000007000000210000000D00000004000000010000000200000000000A000100000000000600470040000A006F005A000600790040000A00A2005A0006000D01FB0006002401FB0006004101FB0006006001FB0006007901FB0006009201FB000600AD01FB000600C801FB0006000002E10106001402FB0006004D022D0206006D022D020A00BA029F020600E402CF020600F402CF0206003803400006003F03400006006903400006007903400000000000010000000000010001000100100022000000050001000100502000000000960082000A000100002200000000960094000A000300C822000000009600AB00120005005823000000009100BA00190006008C25000000008618CF002000080000000100D50000000200DE0000000100D50000000200E50000000100D50000000100EC0000000200F1002900CF0024003100CF0024003900CF0024004100CF0024004900CF0024005100CF0024005900CF0024006100CF0024006900CF0029007100CF0024007900CF002E008100CF0020008900CF0020009100CF0020009900FD02380099000503380099000E03380099001C03380099002403380099002E033800A90045033E00A1004E034200A100560348001100CF00240011005D034E00A10056037300A90073037A002100CF002E0021005D037F00190083039000A10056039500A10056039B000900CF00200020006B0033002E002300DD002E002B00BE002E00630030012E001300D7002E001B00D7002E003300F9002E000B00BE002E003B00D7002E004B00D7002E005B00270140006B00330060006B003300540066008500A1000480000001000000DC13DBA40000000000008B020000020000000000000000000000010037000000000002000000000000000000000001004E00000000000000003C4D6F64756C653E005065727369616E53514C46756E6374696F6E732E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E67004461746554696D6500546F5065727369616E4461746554696D6500546F5065727369616E446174650053716C496E74333200546F5065727369616E446174654E00436F6E76657274546F5065727369616E44617465002E63746F72006461746554696D650069734C6F6E670049734C6F6E6700446174650053686F7274446174650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005065727369616E53514C46756E6374696F6E73004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053797374656D2E476C6F62616C697A6174696F6E005065727369616E43616C656E6461720043616C656E6461720047657459656172004765744D6F6E7468004765744461794F664D6F6E746800476574486F7572004765744D696E757465004765745365636F6E6400537472696E6700496E74333200546F537472696E67005061644C65667400436F6E636174006F705F496D706C6963697400457863657074696F6E005061727365004461794F665765656B006765745F4461794F665765656B0000000000010003200000033A0000032F000003300000093406460628064706010DCC06A9063406460628064706010D2F0648063406460628064706010F3306470620003406460628064706011186064706270631063406460628064706010F7E0646062C06340646062806470601092C06450639064706010F41063106480631062F06CC0646060111270631062F06CC062806470634062A06010B2E0631062F0627062F0601072A06CC063106010B450631062F0627062F06010D340647063106CC0648063106010745064706310601092206280627064606010722063006310601052F06CC0601092806470645064606010B27063306410646062F060100AA1EDC072685E843A869B4D80BE898850008B77A5C561934E0890700021109110D020600011111110D0600020E110D0203200001042001010E04200101020420010108040100000005200108110D0320000E0520020E08030500010E1D0E05000111090E11070B0E124908080808080811091D0E1D0E0C07070E124908080811091D0E0600030E0E0E0E040001080E0500011111080A07060812490808081111042000115D0500010E1D1C0500020E0E0E1C071012491D0E1D0E06110D115D0808080E0E1D0E1D0E115D1D1C1D1C180100135065727369616E53514C46756E6374696F6E7300000501000000001B010016536F6E7920456C656374726F6E6963732C20496E632E00002D010028436F7079726967687420C2A920536F6E7920456C656374726F6E6963732C20496E632E203230303800000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100D42E00000000000000000000EE2E0000002000000000000000000000000000000000000000000000E02E0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000AC0300000000000000000000AC0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100DBA4DC1300000100DBA4DC133F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0040C030000010053007400720069006E006700460069006C00650049006E0066006F000000E8020000010030003000300030003000340062003000000050001700010043006F006D00700061006E0079004E0061006D0065000000000053006F006E007900200045006C0065006300740072006F006E006900630073002C00200049006E0063002E0000000000500014000100460069006C0065004400650073006300720069007000740069006F006E00000000005000650072007300690061006E00530051004C00460075006E006300740069006F006E007300000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0035003000380034002E00340032003200300033000000000050001800010049006E007400650072006E0061006C004E0061006D00650000005000650072007300690061006E00530051004C00460075006E006300740069006F006E0073002E0064006C006C0000007400280001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200053006F006E007900200045006C0065006300740072006F006E006900630073002C00200049006E0063002E002000320030003000380000005800180001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005000650072007300690061006E00530051004C00460075006E006300740069006F006E0073002E0064006C006C000000480014000100500072006F0064007500630074004E0061006D006500000000005000650072007300690061006E00530051004C00460075006E006300740069006F006E007300000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0035003000380034002E00340032003200300033000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0035003000380034002E00340032003200300033000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000003F00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
احتمالا برای اجرای اون به خطای زیر برخورد میکنید
Create ASSEMBLY for assembly 'PersianSQLFunctions' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.
برای حل این مشکل قبل از کد بالا کد زیر رو اجرا کنید
EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;
GO
و بعد مجددا آن را به وضعیت عادی برگردانید
EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
GO
حالا فانکشن مبدل رو اضافه کنید
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[ToPersianDateN](@dateTime [datetime])
RETURNS [int] WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS
EXTERNAL NAME [PersianSQLFunctions].[UserDefinedFunctions].[ToPersianDateN]
برای استفاده هم هر جا لازم بود به این شیوه استفاده کنید
SELECT dbo.ToPersianDateN(YourDate) ...
کد زیر را اجرا کنید تا فانکشن مربوطه ساخته شود.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[FixFarsiChar](@Input NVARCHAR(4000))
returns NVARCHAR(4000)
AS
BEGIN
RETURN
LTRIM(RTRIM(
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(
REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
@Input COLLATE ARAbic_CI_AS
,N'ي',N'ی'),N'ي',N'ی'),N'ى',N'ی'),N'ﻳ',N'ی')
,N'ك',N'ک'),N'ﻜ',N'ک')
,N'ؤ', N'و')
,N'–', N'-'),N'—', N'-'),CHAR(157), N' '),CHAR(172), N' ')
,N'ً', N''),N'ٌ' , N''),N'ٍ', N''),N'َ', N''),N'ô', N''),N'ُ', N''),N'ِ', N'')
, '‌' , ' ')
, CHAR(173) , N' ')
,N' ' , N' '),'«' , '«'),'»' , '»'), ' ', ' '), ' ', ' '), ' ', ' ')
,N'‏' , N' '),N'‫' , N' ')
,NCHAR(8205) , N'')
))
END
وبرای استفاده به عنوان مثال
SELECT dbo.FixFarsiChar(Name) FROM dbo.Customers
یا حتی
UPDATE dbo.Customers SET Name = ( SELECT REPLACE(Name , Name , dbo.FixFarsiChar(Name)))
کد زیر را براساس نام ستون خود تغییر دهید تا بعد از Insert آن ستون به وسیله متد بالا به روزرسانی شود.
CREATE TRIGGER Customer_Trigger
ON dbo.Customers
AFTER INSERT
AS
BEGIN
UPDATE dbo.Customers SET Name = dbo.FixFarsiChar(i.Name)
FROM dbo.Customers
INNER JOIN inserted i on i.Id = Customers.Id
END
برنامه نویس و توسعه دهنده ارشد وب
کارشناس ارشد مدیریت فناوری اطلاعات
برای آگاهی از به روزرسانی های جدید ، ما را در شبکه های اجتماعی دنبال کنید
