現在我這邊只能截取出一個，怎么把里面包含的所有的都截取出來，顯示的結果像上面查詢的一樣，求大佬指導一下
uj5u.com熱心網友回復：
1. 貼出基本的表結構、測驗資料2. 貼出需要的結果
3. 貼出SQL Server版本
select @@version
問好一個問題， 也是一門技術
uj5u.com熱心網友回復：
--創建GS_T_Admission表結構if exists(
select*from sys.objects
where name='GS_T_Admission'
)
drop table GS_T_Admission
go
create table GS_T_Admission
(
AdmissionID int identity(1,1) not null,--ID自增
JSON nvarchar(max) null,--門票資訊JSON
Examine int null,--0為待審核，1審核通過，2審核不通過，3審核取消
NTICKETID int null,--門票ID
Gettime Datetime null,--提交時間
NCREATEBY int null,--提交人
AdmissionTime Datetime null,-- 審核時間
NUPDATEBY int null--審核人
)
go
alter table GS_T_Admission
--設定主鍵
add constraint PK_AdmissionID primary key(AdmissionID)
--測驗你陳述句
INSERT INTO GS_T_Admission(JSON,Examine,NTICKETID,Gettime,NCREATEBY,AdmissionTime,NUPDATEBY) VALUES('{"Tikebase":{"NDATEINCOUNT":0.0,"NISWICKETDAY":0.0,"NDICTBIGCLASS":0.0,"NREGIONID":0.0,"mxvipdesption":0.0,"mxvipdesptionIsNull":true,"SVIPTICKETID":0,"SVIPTICKETIDIsNull":true,"nTicketID":227,"nTicketIDIsNull":false,"sTicketCode":"","sTicketCodeIsNull":true,"sTicketNameCH":"TVM情侶票1","sTicketNameCHIsNull":false,"sTicketNameEN":"","sTicketNameENIsNull":true,"sTicketAnnualCouponName":"TVM情侶票1","sTicketAnnualCouponNameIsNull":false,"sMnemonicCode":"TVMqlp1","sMnemonicCodeIsNull":false,"sPrintDescCH":"僅限情侶使用","sPrintDescCHIsNull":false,"sPrintDescEN":"","sPrintDescENIsNull":true,"nGeneralPrice":0.01,"nGeneralPriceIsNull":false,"nTicketLevel":0,"nTicketLevelIsNull":true,"nInparkPrice":0.0,"nInparkPriceIsNull":true,"nTicketType":0,"nTicketTypeIsNull":true,"sSelectMode":"","sSelectModeIsNull":true,"nMin":1,"nMinIsNull":false,"nMax":0,"nMaxIsNull":false,"nDayLimit":0,"nDayLimitIsNull":false,"nGiftCount":0,"nGiftCountIsNull":true,"nReplaceTicketId":0,"nReplaceTicketIdIsNull":true,"nPackId":0,"nPackIdIsNull":true,"nCreateBy":1708,"nCreateByIsNull":false,"dCreateDate":"2017-04-21T18:54:25","dCreateDateIsNull":false,"nUpdateBy":1708,"nUpdateByIsNull":false,"dUpdateDate":"2018-09-12T17:36:34.3050596","dUpdateDateIsNull":false,"nDelFlag":0,"nDelFlagIsNull":false,"nAllowDiscount":0,"nAllowDiscountIsNull":true,"sCardTypeID":"","sCardTypeIDIsNull":true,"nIsWebSell":0,"nIsWebSellIsNull":true,"nSellPrice":0.0,"nSellPriceIsNull":true,"display_msg":"僅限情侶使用","display_msgIsNull":false,"nIsFinger":0,"nIsFingerIsNull":false,"nPeopleCount":5,"nPeopleCountIsNull":false,"nPrintPrice":0.01,"nPrintPriceIsNull":false,"nIsCar":0,"nIsCarIsNull":true,"nAuditStatus":0,"nAuditStatusIsNull":true,"nIsVIP":0,"nIsVIPIsNull":false,"LOCID":0,"LOCIDIsNull":false,"AREATYPEID":0,"AREATYPEIDIsNull":false,"nIsYearDiscount":0.0,"nIsYearDiscountNull":true,"nIsPASSALL":0.0,"nIsPASSALLIsNull":true,"nIsCamare":0.0,"nIsCamareIsNull":true,"nIsPrintPrice":0.0,"nIsPrintPriceIsNull":true,"nBigTick":1.0,"nBigTickIsNull":false,"nIsTicket":0.0,"nIsTicketIsNull":true,"nEssticketID":"","nEssticketIDIsNull":true,"nCMeal":0.0,"nCMealIsNull":true,"dISCHECK":0.0,"sCHECKER":"","dCHECKDATE":"0001-01-01T00:00:00","NUPDATEFLAG":0,"NISWEBSELLB2C":0,"ISUPDATE":0,"NISTVMSELL":0,"NIDCARDINPARK":0,"UpPlatform":0,"IsCloud":0,"dUpdate":"","dUptime":"","dDndate":"","dDntime":"","EFFECTIVETIME":0,"OnlinTimeLimit":0,"nStoreCount":0,"nStoreCountIsNull":true,"ISSYN":0,"ISSYNIsNull":true,"PrintModID":1006,"PrintModIDIsNull":false,"CLOUDCODE":"0","CLOUDCODEIsNull":false,"ISYEARCARD":0,"ISYEARCARDIsNull":false,"RECEIPTSMOD":0,"RECEIPTSMODIsNull":true,"ISSUBSCRIBE":0,"ISSUBSCRIBEIsNull":true,"XKPRICE":0.0,"XKPRICEIsNull":true,"BKPRICE":0.0,"BKPRICEIsNull":true,"TICKET_SOUND":0,"TICKET_SOUNDIsNull":false,"TICKET_BELONG":1,"TICKET_BELONGIsNull":false,"TICKET_ADMINIC":0,"TICKET_ADMINICIsNull":false,"SINGERINPARK":"","SINGERINPARKIsNull":false,"AUTO_ACTIVATE_USE":"0","AUTO_ACTIVATE_USEIsNull":false,"AUTO_ACTIVATE_TYPE":"","AUTO_ACTIVATE_TYPEIsNull":false,"AUTO_ACTIVATE_TIMES":"","AUTO_ACTIVATE_TIMESIsNull":false},"Validty":{"nIsHoliDays":0.0,"nTicketID":227,"nTicketIDIsNull":false,"nSalaMode":0,"nSalaModeIsNull":false,"nFlag":0,"nFlagIsNull":true,"nDateControl":0,"nDateControlIsNull":false,"sStartDate":"","sStartDateIsNull":false,"sEndDate":"","sEndDateIsNull":false,"nTimeControl":0,"nTimeControlIsNull":false,"sStartTime":"00:00:00","sStartTimeIsNull":false,"sEndTime":"23:59:59","sEndTimeIsNull":false,"nInparkcontrol":0,"nInparkcontrolIsNull":true,"nSaleDays":1,"nSaleDaysIsNull":false,"nCheckDays":0,"nCheckDaysIsNull":true,"nTimes":0,"nTimesIsNull":true,"nType":1,"nTypeIsNull":false,"nWeekControl":0,"nWeekControlIsNull":false,"nMonday":0,"nMondayIsNull":false,"nTuesday":0,"nTuesdayIsNull":false,"nWednesday":0,"nWednesdayIsNull":false,"nThursday":0,"nThursdayIsNull":false,"nFriday":0,"nFridayIsNull":false,"nSaturday":0,"nSaturdayIsNull":false,"nSunday":0,"nSundayIsNull":false,"NSALECONTROL":0,"NSALECONTROLIsNull":true,"SSALESTARTDATE":"","SSALESTARTDATEIsNull":true,"SSALEENDDATE":"","SSALEENDDATEIsNull":true,"HOLDAYSID":0,"HOLDAYSIDNull":false},"Buttons":{"NLEVEL":1,"NLEVELIsNull":false,"NBUTTONID":227,"NBUTTONIDIsNull":false,"SBUTTONCODE":"","SBUTTONCODEIsNull":true,"SBUTTONTITLE":"TVM情侶票1","SBUTTONTITLEIsNull":false,"NTICKETID":"227","NTICKETIDIsNull":false,"SSTYLE":"宋體,9pt/Regular","SSTYLEIsNull":false,"IMAGE":"","IMAGEIsNull":true,"SCANSEE":"0","SCANSEEIsNull":false,"SCANSALE":"0","SCANSALEIsNull":false,"SSALEPWD":"","SSALEPWDIsNull":false,"NCREATEBY":1708,"NCREATEBYIsNull":false,"DCREATEDT":"2017-04-21T18:54:25","DCREATEDTIsNull":false,"NUPDATEBY":1708,"NUPDATEBYIsNull":false,"DUPDATEDT":"2017-04-21T18:54:25","DUPDATEDTIsNull":false,"SBUTTONTYPEIsNull":false,"SBUTTONTYPE":"0","ISSHOWDATE":0,"ISSHOWDATEIsNull":false,"DVALIDITYSTART":"0001-01-01T00:00:00","DVALIDITYSTARTIsNull":true,"DVALIDITYEND":"0001-01-01T00:00:00","DVALIDITYENDIsNull":true,"BARKCOLOR":"0,255,255,255","BARKCOLORIsNull":false,"FONTCOLOR":"0,0,0,0","FONTCOLORIsNull":false,"NSORT":0,"NSORTIsNull":false,"TICKET_EMPOWER":0,"TICKET_EMPOWERIsNull":false,"ISSHOWTIME":0,"SHOWBEGINTIME":"","SHOWENDTIME":"","ISSHOWWEEK":1,"SHOWWEEKMONDAY":1,"SHOWWEEKTUESDAY":0,"SHOWWEEKWEDNESDAY":1,"SHOWWEEKTHURSDAY":1,"SHOWWEEKFRIDAY":1,"SHOWWEEKSATURDAY":0,"SHOWWEEKSUNDAY":0,"HOLIDAYSID":0},"Inparcount":{"nTciektID":227,"nTciektIDIsNull":false,"nInParkCount":1,"nInParkCountIsNull":false},"Inparkinfo":[{"nTicketID":227,"nTicketIDIsNull":false,"nGzoneID":2555,"nGzoneIDIsNull":false,"maxCOUNT":1211,"maxCOUNTIsNull":false,"dayCount":1211,"dayCountIsNull":false},{"nTicketID":227,"nTicketIDIsNull":false,"nGzoneID":2640,"nGzoneIDIsNull":false,"maxCOUNT":100,"maxCOUNTIsNull":false,"dayCount":100,"dayCountIsNull":false},{"nTicketID":227,"nTicketIDIsNull":false,"nGzoneID":3467,"nGzoneIDIsNull":false,"maxCOUNT":1,"maxCOUNTIsNull":false,"dayCount":1,"dayCountIsNull":false}],"TIcketpark":[],"Ticketparkprice":[]}','0','227','2018/9/12 17:24:37','1708','','')
--截取陳述句
SELECT
SUBSTRING(SUBSTRING(JSON,CHARINDEX('NGZONEID',JSON)+len('NGZONEID') + 2,len(JSON)),0,
CHARINDEX(',',
SUBSTRING(JSON,CHARINDEX('NGZONEID',JSON) --第一次出現 PARTNERNAME 的位置
+len('NGZONEID') --PARTNERNAME 長度
+ 2, -- “,"
len(JSON) --到最后位置
))
)
FROM GS_T_Admission where NTICKETID =227
--結果如上圖NGZONEID的資料
uj5u.com熱心網友回復：
--創建GS_T_Admission表結構if exists(
select*from sys.objects
where name='GS_T_Admission'
)
drop table GS_T_Admission
go
create table GS_T_Admission
(
AdmissionID int identity(1,1) not null,--ID自增
JSON nvarchar(max) null,--門票資訊JSON
Examine int null,--0為待審核，1審核通過，2審核不通過，3審核取消
NTICKETID int null,--門票ID
Gettime Datetime null,--提交時間
NCREATEBY int null,--提交人
AdmissionTime Datetime null,-- 審核時間
NUPDATEBY int null--審核人
)
go
alter table GS_T_Admission
--設定主鍵
add constraint PK_AdmissionID primary key(AdmissionID)
--測驗你陳述句
INSERT INTO GS_T_Admission(JSON,Examine,NTICKETID,Gettime,NCREATEBY,AdmissionTime,NUPDATEBY) VALUES('{"Tikebase":{"NDATEINCOUNT":0.0,"NISWICKETDAY":0.0,"NDICTBIGCLASS":0.0,"NREGIONID":0.0,"mxvipdesption":0.0,"mxvipdesptionIsNull":true,"SVIPTICKETID":0,"SVIPTICKETIDIsNull":true,"nTicketID":227,"nTicketIDIsNull":false,"sTicketCode":"","sTicketCodeIsNull":true,"sTicketNameCH":"TVM情侶票1","sTicketNameCHIsNull":false,"sTicketNameEN":"","sTicketNameENIsNull":true,"sTicketAnnualCouponName":"TVM情侶票1","sTicketAnnualCouponNameIsNull":false,"sMnemonicCode":"TVMqlp1","sMnemonicCodeIsNull":false,"sPrintDescCH":"僅限情侶使用","sPrintDescCHIsNull":false,"sPrintDescEN":"","sPrintDescENIsNull":true,"nGeneralPrice":0.01,"nGeneralPriceIsNull":false,"nTicketLevel":0,"nTicketLevelIsNull":true,"nInparkPrice":0.0,"nInparkPriceIsNull":true,"nTicketType":0,"nTicketTypeIsNull":true,"sSelectMode":"","sSelectModeIsNull":true,"nMin":1,"nMinIsNull":false,"nMax":0,"nMaxIsNull":false,"nDayLimit":0,"nDayLimitIsNull":false,"nGiftCount":0,"nGiftCountIsNull":true,"nReplaceTicketId":0,"nReplaceTicketIdIsNull":true,"nPackId":0,"nPackIdIsNull":true,"nCreateBy":1708,"nCreateByIsNull":false,"dCreateDate":"2017-04-21T18:54:25","dCreateDateIsNull":false,"nUpdateBy":1708,"nUpdateByIsNull":false,"dUpdateDate":"2018-09-12T17:36:34.3050596","dUpdateDateIsNull":false,"nDelFlag":0,"nDelFlagIsNull":false,"nAllowDiscount":0,"nAllowDiscountIsNull":true,"sCardTypeID":"","sCardTypeIDIsNull":true,"nIsWebSell":0,"nIsWebSellIsNull":true,"nSellPrice":0.0,"nSellPriceIsNull":true,"display_msg":"僅限情侶使用","display_msgIsNull":false,"nIsFinger":0,"nIsFingerIsNull":false,"nPeopleCount":5,"nPeopleCountIsNull":false,"nPrintPrice":0.01,"nPrintPriceIsNull":false,"nIsCar":0,"nIsCarIsNull":true,"nAuditStatus":0,"nAuditStatusIsNull":true,"nIsVIP":0,"nIsVIPIsNull":false,"LOCID":0,"LOCIDIsNull":false,"AREATYPEID":0,"AREATYPEIDIsNull":false,"nIsYearDiscount":0.0,"nIsYearDiscountNull":true,"nIsPASSALL":0.0,"nIsPASSALLIsNull":true,"nIsCamare":0.0,"nIsCamareIsNull":true,"nIsPrintPrice":0.0,"nIsPrintPriceIsNull":true,"nBigTick":1.0,"nBigTickIsNull":false,"nIsTicket":0.0,"nIsTicketIsNull":true,"nEssticketID":"","nEssticketIDIsNull":true,"nCMeal":0.0,"nCMealIsNull":true,"dISCHECK":0.0,"sCHECKER":"","dCHECKDATE":"0001-01-01T00:00:00","NUPDATEFLAG":0,"NISWEBSELLB2C":0,"ISUPDATE":0,"NISTVMSELL":0,"NIDCARDINPARK":0,"UpPlatform":0,"IsCloud":0,"dUpdate":"","dUptime":"","dDndate":"","dDntime":"","EFFECTIVETIME":0,"OnlinTimeLimit":0,"nStoreCount":0,"nStoreCountIsNull":true,"ISSYN":0,"ISSYNIsNull":true,"PrintModID":1006,"PrintModIDIsNull":false,"CLOUDCODE":"0","CLOUDCODEIsNull":false,"ISYEARCARD":0,"ISYEARCARDIsNull":false,"RECEIPTSMOD":0,"RECEIPTSMODIsNull":true,"ISSUBSCRIBE":0,"ISSUBSCRIBEIsNull":true,"XKPRICE":0.0,"XKPRICEIsNull":true,"BKPRICE":0.0,"BKPRICEIsNull":true,"TICKET_SOUND":0,"TICKET_SOUNDIsNull":false,"TICKET_BELONG":1,"TICKET_BELONGIsNull":false,"TICKET_ADMINIC":0,"TICKET_ADMINICIsNull":false,"SINGERINPARK":"","SINGERINPARKIsNull":false,"AUTO_ACTIVATE_USE":"0","AUTO_ACTIVATE_USEIsNull":false,"AUTO_ACTIVATE_TYPE":"","AUTO_ACTIVATE_TYPEIsNull":false,"AUTO_ACTIVATE_TIMES":"","AUTO_ACTIVATE_TIMESIsNull":false},"Validty":{"nIsHoliDays":0.0,"nTicketID":227,"nTicketIDIsNull":false,"nSalaMode":0,"nSalaModeIsNull":false,"nFlag":0,"nFlagIsNull":true,"nDateControl":0,"nDateControlIsNull":false,"sStartDate":"","sStartDateIsNull":false,"sEndDate":"","sEndDateIsNull":false,"nTimeControl":0,"nTimeControlIsNull":false,"sStartTime":"00:00:00","sStartTimeIsNull":false,"sEndTime":"23:59:59","sEndTimeIsNull":false,"nInparkcontrol":0,"nInparkcontrolIsNull":true,"nSaleDays":1,"nSaleDaysIsNull":false,"nCheckDays":0,"nCheckDaysIsNull":true,"nTimes":0,"nTimesIsNull":true,"nType":1,"nTypeIsNull":false,"nWeekControl":0,"nWeekControlIsNull":false,"nMonday":0,"nMondayIsNull":false,"nTuesday":0,"nTuesdayIsNull":false,"nWednesday":0,"nWednesdayIsNull":false,"nThursday":0,"nThursdayIsNull":false,"nFriday":0,"nFridayIsNull":false,"nSaturday":0,"nSaturdayIsNull":false,"nSunday":0,"nSundayIsNull":false,"NSALECONTROL":0,"NSALECONTROLIsNull":true,"SSALESTARTDATE":"","SSALESTARTDATEIsNull":true,"SSALEENDDATE":"","SSALEENDDATEIsNull":true,"HOLDAYSID":0,"HOLDAYSIDNull":false},"Buttons":{"NLEVEL":1,"NLEVELIsNull":false,"NBUTTONID":227,"NBUTTONIDIsNull":false,"SBUTTONCODE":"","SBUTTONCODEIsNull":true,"SBUTTONTITLE":"TVM情侶票1","SBUTTONTITLEIsNull":false,"NTICKETID":"227","NTICKETIDIsNull":false,"SSTYLE":"宋體,9pt/Regular","SSTYLEIsNull":false,"IMAGE":"","IMAGEIsNull":true,"SCANSEE":"0","SCANSEEIsNull":false,"SCANSALE":"0","SCANSALEIsNull":false,"SSALEPWD":"","SSALEPWDIsNull":false,"NCREATEBY":1708,"NCREATEBYIsNull":false,"DCREATEDT":"2017-04-21T18:54:25","DCREATEDTIsNull":false,"NUPDATEBY":1708,"NUPDATEBYIsNull":false,"DUPDATEDT":"2017-04-21T18:54:25","DUPDATEDTIsNull":false,"SBUTTONTYPEIsNull":false,"SBUTTONTYPE":"0","ISSHOWDATE":0,"ISSHOWDATEIsNull":false,"DVALIDITYSTART":"0001-01-01T00:00:00","DVALIDITYSTARTIsNull":true,"DVALIDITYEND":"0001-01-01T00:00:00","DVALIDITYENDIsNull":true,"BARKCOLOR":"0,255,255,255","BARKCOLORIsNull":false,"FONTCOLOR":"0,0,0,0","FONTCOLORIsNull":false,"NSORT":0,"NSORTIsNull":false,"TICKET_EMPOWER":0,"TICKET_EMPOWERIsNull":false,"ISSHOWTIME":0,"SHOWBEGINTIME":"","SHOWENDTIME":"","ISSHOWWEEK":1,"SHOWWEEKMONDAY":1,"SHOWWEEKTUESDAY":0,"SHOWWEEKWEDNESDAY":1,"SHOWWEEKTHURSDAY":1,"SHOWWEEKFRIDAY":1,"SHOWWEEKSATURDAY":0,"SHOWWEEKSUNDAY":0,"HOLIDAYSID":0},"Inparcount":{"nTciektID":227,"nTciektIDIsNull":false,"nInParkCount":1,"nInParkCountIsNull":false},"Inparkinfo":[{"nTicketID":227,"nTicketIDIsNull":false,"nGzoneID":2555,"nGzoneIDIsNull":false,"maxCOUNT":1211,"maxCOUNTIsNull":false,"dayCount":1211,"dayCountIsNull":false},{"nTicketID":227,"nTicketIDIsNull":false,"nGzoneID":2640,"nGzoneIDIsNull":false,"maxCOUNT":100,"maxCOUNTIsNull":false,"dayCount":100,"dayCountIsNull":false},{"nTicketID":227,"nTicketIDIsNull":false,"nGzoneID":3467,"nGzoneIDIsNull":false,"maxCOUNT":1,"maxCOUNTIsNull":false,"dayCount":1,"dayCountIsNull":false}],"TIcketpark":[],"Ticketparkprice":[]}','0','227','2018/9/12 17:24:37','1708','','')
--截取陳述句
SELECT
SUBSTRING(SUBSTRING(JSON,CHARINDEX('NGZONEID',JSON)+len('NGZONEID') + 2,len(JSON)),0,
CHARINDEX(',',
SUBSTRING(JSON,CHARINDEX('NGZONEID',JSON) --第一次出現 PARTNERNAME 的位置
+len('NGZONEID') --PARTNERNAME 長度
+ 2, -- “,"
len(JSON) --到最后位置
))
)
FROM GS_T_Admission where NTICKETID =227
--結果如上圖NGZONEID的資料
uj5u.com熱心網友回復：
大佬我貼出來了
uj5u.com熱心網友回復：
--創建GS_T_Admission表結構
if exists(
select*from sys.objects
where name='GS_T_Admission'
)
drop table GS_T_Admission
go
create table GS_T_Admission
(
AdmissionID int identity(1,1) not null,--ID自增
JSON nvarchar(max) null,--門票資訊JSON
Examine int null,--0為待審核，1審核通過，2審核不通過，3審核取消
NTICKETID int null,--門票ID
Gettime Datetime null,--提交時間
NCREATEBY int null,--提交人
AdmissionTime Datetime null,-- 審核時間
NUPDATEBY int null--審核人
)
go
alter table GS_T_Admission
--設定主鍵
add constraint PK_AdmissionID primary key(AdmissionID)
--測驗陳述句
INSERT INTO GS_T_Admission(JSON,Examine,NTICKETID,Gettime,NCREATEBY,AdmissionTime,NUPDATEBY) VALUES('{"Tikebase":{"NDATEINCOUNT":0.0,"NISWICKETDAY":0.0,"NDICTBIGCLASS":0.0,"NREGIONID":0.0,"mxvipdesption":0.0,"mxvipdesptionIsNull":true,"SVIPTICKETID":0,"SVIPTICKETIDIsNull":true,"nTicketID":227,"nTicketIDIsNull":false,"sTicketCode":"","sTicketCodeIsNull":true,"sTicketNameCH":"TVM情侶票1","sTicketNameCHIsNull":false,"sTicketNameEN":"","sTicketNameENIsNull":true,"sTicketAnnualCouponName":"TVM情侶票1","sTicketAnnualCouponNameIsNull":false,"sMnemonicCode":"TVMqlp1","sMnemonicCodeIsNull":false,"sPrintDescCH":"僅限情侶使用","sPrintDescCHIsNull":false,"sPrintDescEN":"","sPrintDescENIsNull":true,"nGeneralPrice":0.01,"nGeneralPriceIsNull":false,"nTicketLevel":0,"nTicketLevelIsNull":true,"nInparkPrice":0.0,"nInparkPriceIsNull":true,"nTicketType":0,"nTicketTypeIsNull":true,"sSelectMode":"","sSelectModeIsNull":true,"nMin":1,"nMinIsNull":false,"nMax":0,"nMaxIsNull":false,"nDayLimit":0,"nDayLimitIsNull":false,"nGiftCount":0,"nGiftCountIsNull":true,"nReplaceTicketId":0,"nReplaceTicketIdIsNull":true,"nPackId":0,"nPackIdIsNull":true,"nCreateBy":1708,"nCreateByIsNull":false,"dCreateDate":"2017-04-21T18:54:25","dCreateDateIsNull":false,"nUpdateBy":1708,"nUpdateByIsNull":false,"dUpdateDate":"2018-09-12T17:36:34.3050596","dUpdateDateIsNull":false,"nDelFlag":0,"nDelFlagIsNull":false,"nAllowDiscount":0,"nAllowDiscountIsNull":true,"sCardTypeID":"","sCardTypeIDIsNull":true,"nIsWebSell":0,"nIsWebSellIsNull":true,"nSellPrice":0.0,"nSellPriceIsNull":true,"display_msg":"僅限情侶使用","display_msgIsNull":false,"nIsFinger":0,"nIsFingerIsNull":false,"nPeopleCount":5,"nPeopleCountIsNull":false,"nPrintPrice":0.01,"nPrintPriceIsNull":false,"nIsCar":0,"nIsCarIsNull":true,"nAuditStatus":0,"nAuditStatusIsNull":true,"nIsVIP":0,"nIsVIPIsNull":false,"LOCID":0,"LOCIDIsNull":false,"AREATYPEID":0,"AREATYPEIDIsNull":false,"nIsYearDiscount":0.0,"nIsYearDiscountNull":true,"nIsPASSALL":0.0,"nIsPASSALLIsNull":true,"nIsCamare":0.0,"nIsCamareIsNull":true,"nIsPrintPrice":0.0,"nIsPrintPriceIsNull":true,"nBigTick":1.0,"nBigTickIsNull":false,"nIsTicket":0.0,"nIsTicketIsNull":true,"nEssticketID":"","nEssticketIDIsNull":true,"nCMeal":0.0,"nCMealIsNull":true,"dISCHECK":0.0,"sCHECKER":"","dCHECKDATE":"0001-01-01T00:00:00","NUPDATEFLAG":0,"NISWEBSELLB2C":0,"ISUPDATE":0,"NISTVMSELL":0,"NIDCARDINPARK":0,"UpPlatform":0,"IsCloud":0,"dUpdate":"","dUptime":"","dDndate":"","dDntime":"","EFFECTIVETIME":0,"OnlinTimeLimit":0,"nStoreCount":0,"nStoreCountIsNull":true,"ISSYN":0,"ISSYNIsNull":true,"PrintModID":1006,"PrintModIDIsNull":false,"CLOUDCODE":"0","CLOUDCODEIsNull":false,"ISYEARCARD":0,"ISYEARCARDIsNull":false,"RECEIPTSMOD":0,"RECEIPTSMODIsNull":true,"ISSUBSCRIBE":0,"ISSUBSCRIBEIsNull":true,"XKPRICE":0.0,"XKPRICEIsNull":true,"BKPRICE":0.0,"BKPRICEIsNull":true,"TICKET_SOUND":0,"TICKET_SOUNDIsNull":false,"TICKET_BELONG":1,"TICKET_BELONGIsNull":false,"TICKET_ADMINIC":0,"TICKET_ADMINICIsNull":false,"SINGERINPARK":"","SINGERINPARKIsNull":false,"AUTO_ACTIVATE_USE":"0","AUTO_ACTIVATE_USEIsNull":false,"AUTO_ACTIVATE_TYPE":"","AUTO_ACTIVATE_TYPEIsNull":false,"AUTO_ACTIVATE_TIMES":"","AUTO_ACTIVATE_TIMESIsNull":false},"Validty":{"nIsHoliDays":0.0,"nTicketID":227,"nTicketIDIsNull":false,"nSalaMode":0,"nSalaModeIsNull":false,"nFlag":0,"nFlagIsNull":true,"nDateControl":0,"nDateControlIsNull":false,"sStartDate":"","sStartDateIsNull":false,"sEndDate":"","sEndDateIsNull":false,"nTimeControl":0,"nTimeControlIsNull":false,"sStartTime":"00:00:00","sStartTimeIsNull":false,"sEndTime":"23:59:59","sEndTimeIsNull":false,"nInparkcontrol":0,"nInparkcontrolIsNull":true,"nSaleDays":1,"nSaleDaysIsNull":false,"nCheckDays":0,"nCheckDaysIsNull":true,"nTimes":0,"nTimesIsNull":true,"nType":1,"nTypeIsNull":false,"nWeekControl":0,"nWeekControlIsNull":false,"nMonday":0,"nMondayIsNull":false,"nTuesday":0,"nTuesdayIsNull":false,"nWednesday":0,"nWednesdayIsNull":false,"nThursday":0,"nThursdayIsNull":false,"nFriday":0,"nFridayIsNull":false,"nSaturday":0,"nSaturdayIsNull":false,"nSunday":0,"nSundayIsNull":false,"NSALECONTROL":0,"NSALECONTROLIsNull":true,"SSALESTARTDATE":"","SSALESTARTDATEIsNull":true,"SSALEENDDATE":"","SSALEENDDATEIsNull":true,"HOLDAYSID":0,"HOLDAYSIDNull":false},"Buttons":{"NLEVEL":1,"NLEVELIsNull":false,"NBUTTONID":227,"NBUTTONIDIsNull":false,"SBUTTONCODE":"","SBUTTONCODEIsNull":true,"SBUTTONTITLE":"TVM情侶票1","SBUTTONTITLEIsNull":false,"NTICKETID":"227","NTICKETIDIsNull":false,"SSTYLE":"宋體,9pt/Regular","SSTYLEIsNull":false,"IMAGE":"","IMAGEIsNull":true,"SCANSEE":"0","SCANSEEIsNull":false,"SCANSALE":"0","SCANSALEIsNull":false,"SSALEPWD":"","SSALEPWDIsNull":false,"NCREATEBY":1708,"NCREATEBYIsNull":false,"DCREATEDT":"2017-04-21T18:54:25","DCREATEDTIsNull":false,"NUPDATEBY":1708,"NUPDATEBYIsNull":false,"DUPDATEDT":"2017-04-21T18:54:25","DUPDATEDTIsNull":false,"SBUTTONTYPEIsNull":false,"SBUTTONTYPE":"0","ISSHOWDATE":0,"ISSHOWDATEIsNull":false,"DVALIDITYSTART":"0001-01-01T00:00:00","DVALIDITYSTARTIsNull":true,"DVALIDITYEND":"0001-01-01T00:00:00","DVALIDITYENDIsNull":true,"BARKCOLOR":"0,255,255,255","BARKCOLORIsNull":false,"FONTCOLOR":"0,0,0,0","FONTCOLORIsNull":false,"NSORT":0,"NSORTIsNull":false,"TICKET_EMPOWER":0,"TICKET_EMPOWERIsNull":false,"ISSHOWTIME":0,"SHOWBEGINTIME":"","SHOWENDTIME":"","ISSHOWWEEK":1,"SHOWWEEKMONDAY":1,"SHOWWEEKTUESDAY":0,"SHOWWEEKWEDNESDAY":1,"SHOWWEEKTHURSDAY":1,"SHOWWEEKFRIDAY":1,"SHOWWEEKSATURDAY":0,"SHOWWEEKSUNDAY":0,"HOLIDAYSID":0},"Inparcount":{"nTciektID":227,"nTciektIDIsNull":false,"nInParkCount":1,"nInParkCountIsNull":false},"Inparkinfo":[{"nTicketID":227,"nTicketIDIsNull":false,"nGzoneID":2555,"nGzoneIDIsNull":false,"maxCOUNT":1211,"maxCOUNTIsNull":false,"dayCount":1211,"dayCountIsNull":false},{"nTicketID":227,"nTicketIDIsNull":false,"nGzoneID":2640,"nGzoneIDIsNull":false,"maxCOUNT":100,"maxCOUNTIsNull":false,"dayCount":100,"dayCountIsNull":false},{"nTicketID":227,"nTicketIDIsNull":false,"nGzoneID":3467,"nGzoneIDIsNull":false,"maxCOUNT":1,"maxCOUNTIsNull":false,"dayCount":1,"dayCountIsNull":false}],"TIcketpark":[],"Ticketparkprice":[]}','0','227','2018/9/12 17:24:37','1708','','')
--------------- 以上為測驗資料 --------------
------ 1. 增加表值函式
IF OBJECT_ID('dbo.Fun_GetListFromJson') IS NOT NULL
DROP FUNCTION dbo.Fun_GetListFromJson
GO
-- =============================================
-- Author: yenange
-- Create date: 2018-09-16
-- Description: 從json獲取串列
-- =============================================
CREATE FUNCTION dbo.Fun_GetListFromJson
(
@s NVARCHAR(MAX)
)
RETURNS
@t TABLE
(
item VARCHAR(200)
)
AS
BEGIN
DECLARE @d INT,@d2 INT
SET @d = CHARINDEX('Inparkinfo',@s)
IF @d>0
BEGIN
SET @s=SUBSTRING(@s,@d,LEN(@s))
WHILE CHARINDEX('nGzoneID"',@s)>0
BEGIN
SET @s=SUBSTRING(@s,CHARINDEX('nGzoneID"',@s),LEN(@s))
INSERT INTO @t(item)
SELECT rtrim(LTRIM(SUBSTRING(@s,CHARINDEX(':',@s)+1,CHARINDEX(',',@s)-CHARINDEX(':',@s)-1)))
SET @s=SUBSTRING(@s,CHARINDEX(',',@s),LEN(@s))
END
END
RETURN
END
GO
------2. 查詢
SELECT * FROM GS_T_Admission AS a CROSS APPLY dbo.Fun_GetListFromJson(a.[JSON]) AS f
你也可以用 sqlclr 來做， 這樣更精準。
或者升級到 SQLServer2016 ， 用 新增的 json 功能來決議。
uj5u.com熱心網友回復：
大佬可以 改成這種格式嗎，只讓他的 JSON 和 要截取的欄位變成 變數， 你那個 好復雜，而且不能給別的用，希望能夠通用
ALTER FUNCTION [dbo].[F_GetStrS] (@Str NVARCHAR(MAX),@PARTNERNAME NVARCHAR(max))
--截取帶沒有用""Json字串
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @Str =
(
SELECT
CASE
WHEN CHARINDEX(@PARTNERNAME, @Str) = 0 --沒有當前字符
THEN ''
ELSE
SUBSTRING(SUBSTRING( @Str, CHARINDEX(@PARTNERNAME, @Str) + LEN(@PARTNERNAME) + 2, LEN(@Str)), 0,
CHARINDEX( ',', SUBSTRING(
@Str,
CHARINDEX(@PARTNERNAME, @Str) --第一次出現 PARTNERNAME 的位置
+ LEN(@PARTNERNAME) --PARTNERNAME 長度
+ 2, -- “,"
LEN(@Str)))
)
END
);
RETURN @Str;
END;
SELECT
dbo.F_GetStrS(JSON,'NGZONEID')AS NGZONEID
FROM GS_T_Admission WHERE NTICKETID=227
類似于這樣 查詢的時候只要輸入 JSON 和 要截取的欄位
uj5u.com熱心網友回復：
因為我還想要達到一個 能有多列的效果
ALTER FUNCTION dbo.Fun_GetListFromJson
(
@s NVARCHAR(MAX),
@table NVARCHAR(MAX),
@coulm NVARCHAR(MAX)
)
RETURNS
@t TABLE
(
item VARCHAR(200)
)
AS
BEGIN
DECLARE @d INT,@d2 INT
SET @d = CHARINDEX(@table,@s)
IF @d>0
BEGIN
SET @s=SUBSTRING(@s,@d,LEN(@s))
WHILE CHARINDEX(@coulm,@s)>0
BEGIN
SET @s=SUBSTRING(@s,CHARINDEX(@coulm,@s),LEN(@s))
INSERT INTO @t(item)
SELECT rtrim(LTRIM(SUBSTRING(@s,CHARINDEX(':',@s)+1,CHARINDEX(',',@s)-CHARINDEX(':',@s)-1)))
SET @s=SUBSTRING(@s,CHARINDEX(',',@s),LEN(@s))
END
END
RETURN
END
GO
我應該要怎么改
uj5u.com熱心網友回復：
拜托， 您另請高明吧。這種非結構性的資料不好處理， 即使寫成了， 也容易出現問題。
如果是經常需要查詢的， 不應該定義為 json， 而是采用表關聯。
如果一定要用 json , 我前面也說了， 升級到 sqlserver 2016,
或寫 sqlclr ：https://blog.csdn.net/yenange/article/details/47659385
uj5u.com熱心網友回復：
那么我怎么讓他顯示多列呢
uj5u.com熱心網友回復：
比如說 我第二列 是
SELECT item
FROM GS_T_Admission AS a CROSS APPLY dbo.Fun_GetListFromJson(a.[JSON],'Inparkinfo','maxcount"') d
where NTICKETID =227
這個
uj5u.com熱心網友回復：
老哥 別走，我需要你的幫助
uj5u.com熱心網友回復：
--創建GS_T_Admission表結構
if exists(
select*from sys.objects
where name='GS_T_Admission'
)
drop table GS_T_Admission
go
create table GS_T_Admission
(
AdmissionID int identity(1,1) not null,--ID自增
JSON nvarchar(max) null,--門票資訊JSON
Examine int null,--0為待審核，1審核通過，2審核不通過，3審核取消
NTICKETID int null,--門票ID
Gettime Datetime null,--提交時間
NCREATEBY int null,--提交人
AdmissionTime Datetime null,-- 審核時間
NUPDATEBY int null--審核人
)
go
alter table GS_T_Admission
--設定主鍵
add constraint PK_AdmissionID primary key(AdmissionID)
--測驗陳述句
INSERT INTO GS_T_Admission(JSON,Examine,NTICKETID,Gettime,NCREATEBY,AdmissionTime,NUPDATEBY) VALUES('{"Tikebase":{"NDATEINCOUNT":0.0,"NISWICKETDAY":0.0,"NDICTBIGCLASS":0.0,"NREGIONID":0.0,"mxvipdesption":0.0,"mxvipdesptionIsNull":true,"SVIPTICKETID":0,"SVIPTICKETIDIsNull":true,"nTicketID":227,"nTicketIDIsNull":false,"sTicketCode":"","sTicketCodeIsNull":true,"sTicketNameCH":"TVM情侶票1","sTicketNameCHIsNull":false,"sTicketNameEN":"","sTicketNameENIsNull":true,"sTicketAnnualCouponName":"TVM情侶票1","sTicketAnnualCouponNameIsNull":false,"sMnemonicCode":"TVMqlp1","sMnemonicCodeIsNull":false,"sPrintDescCH":"僅限情侶使用","sPrintDescCHIsNull":false,"sPrintDescEN":"","sPrintDescENIsNull":true,"nGeneralPrice":0.01,"nGeneralPriceIsNull":false,"nTicketLevel":0,"nTicketLevelIsNull":true,"nInparkPrice":0.0,"nInparkPriceIsNull":true,"nTicketType":0,"nTicketTypeIsNull":true,"sSelectMode":"","sSelectModeIsNull":true,"nMin":1,"nMinIsNull":false,"nMax":0,"nMaxIsNull":false,"nDayLimit":0,"nDayLimitIsNull":false,"nGiftCount":0,"nGiftCountIsNull":true,"nReplaceTicketId":0,"nReplaceTicketIdIsNull":true,"nPackId":0,"nPackIdIsNull":true,"nCreateBy":1708,"nCreateByIsNull":false,"dCreateDate":"2017-04-21T18:54:25","dCreateDateIsNull":false,"nUpdateBy":1708,"nUpdateByIsNull":false,"dUpdateDate":"2018-09-12T17:36:34.3050596","dUpdateDateIsNull":false,"nDelFlag":0,"nDelFlagIsNull":false,"nAllowDiscount":0,"nAllowDiscountIsNull":true,"sCardTypeID":"","sCardTypeIDIsNull":true,"nIsWebSell":0,"nIsWebSellIsNull":true,"nSellPrice":0.0,"nSellPriceIsNull":true,"display_msg":"僅限情侶使用","display_msgIsNull":false,"nIsFinger":0,"nIsFingerIsNull":false,"nPeopleCount":5,"nPeopleCountIsNull":false,"nPrintPrice":0.01,"nPrintPriceIsNull":false,"nIsCar":0,"nIsCarIsNull":true,"nAuditStatus":0,"nAuditStatusIsNull":true,"nIsVIP":0,"nIsVIPIsNull":false,"LOCID":0,"LOCIDIsNull":false,"AREATYPEID":0,"AREATYPEIDIsNull":false,"nIsYearDiscount":0.0,"nIsYearDiscountNull":true,"nIsPASSALL":0.0,"nIsPASSALLIsNull":true,"nIsCamare":0.0,"nIsCamareIsNull":true,"nIsPrintPrice":0.0,"nIsPrintPriceIsNull":true,"nBigTick":1.0,"nBigTickIsNull":false,"nIsTicket":0.0,"nIsTicketIsNull":true,"nEssticketID":"","nEssticketIDIsNull":true,"nCMeal":0.0,"nCMealIsNull":true,"dISCHECK":0.0,"sCHECKER":"","dCHECKDATE":"0001-01-01T00:00:00","NUPDATEFLAG":0,"NISWEBSELLB2C":0,"ISUPDATE":0,"NISTVMSELL":0,"NIDCARDINPARK":0,"UpPlatform":0,"IsCloud":0,"dUpdate":"","dUptime":"","dDndate":"","dDntime":"","EFFECTIVETIME":0,"OnlinTimeLimit":0,"nStoreCount":0,"nStoreCountIsNull":true,"ISSYN":0,"ISSYNIsNull":true,"PrintModID":1006,"PrintModIDIsNull":false,"CLOUDCODE":"0","CLOUDCODEIsNull":false,"ISYEARCARD":0,"ISYEARCARDIsNull":false,"RECEIPTSMOD":0,"RECEIPTSMODIsNull":true,"ISSUBSCRIBE":0,"ISSUBSCRIBEIsNull":true,"XKPRICE":0.0,"XKPRICEIsNull":true,"BKPRICE":0.0,"BKPRICEIsNull":true,"TICKET_SOUND":0,"TICKET_SOUNDIsNull":false,"TICKET_BELONG":1,"TICKET_BELONGIsNull":false,"TICKET_ADMINIC":0,"TICKET_ADMINICIsNull":false,"SINGERINPARK":"","SINGERINPARKIsNull":false,"AUTO_ACTIVATE_USE":"0","AUTO_ACTIVATE_USEIsNull":false,"AUTO_ACTIVATE_TYPE":"","AUTO_ACTIVATE_TYPEIsNull":false,"AUTO_ACTIVATE_TIMES":"","AUTO_ACTIVATE_TIMESIsNull":false},"Validty":{"nIsHoliDays":0.0,"nTicketID":227,"nTicketIDIsNull":false,"nSalaMode":0,"nSalaModeIsNull":false,"nFlag":0,"nFlagIsNull":true,"nDateControl":0,"nDateControlIsNull":false,"sStartDate":"","sStartDateIsNull":false,"sEndDate":"","sEndDateIsNull":false,"nTimeControl":0,"nTimeControlIsNull":false,"sStartTime":"00:00:00","sStartTimeIsNull":false,"sEndTime":"23:59:59","sEndTimeIsNull":false,"nInparkcontrol":0,"nInparkcontrolIsNull":true,"nSaleDays":1,"nSaleDaysIsNull":false,"nCheckDays":0,"nCheckDaysIsNull":true,"nTimes":0,"nTimesIsNull":true,"nType":1,"nTypeIsNull":false,"nWeekControl":0,"nWeekControlIsNull":false,"nMonday":0,"nMondayIsNull":false,"nTuesday":0,"nTuesdayIsNull":false,"nWednesday":0,"nWednesdayIsNull":false,"nThursday":0,"nThursdayIsNull":false,"nFriday":0,"nFridayIsNull":false,"nSaturday":0,"nSaturdayIsNull":false,"nSunday":0,"nSundayIsNull":false,"NSALECONTROL":0,"NSALECONTROLIsNull":true,"SSALESTARTDATE":"","SSALESTARTDATEIsNull":true,"SSALEENDDATE":"","SSALEENDDATEIsNull":true,"HOLDAYSID":0,"HOLDAYSIDNull":false},"Buttons":{"NLEVEL":1,"NLEVELIsNull":false,"NBUTTONID":227,"NBUTTONIDIsNull":false,"SBUTTONCODE":"","SBUTTONCODEIsNull":true,"SBUTTONTITLE":"TVM情侶票1","SBUTTONTITLEIsNull":false,"NTICKETID":"227","NTICKETIDIsNull":false,"SSTYLE":"宋體,9pt/Regular","SSTYLEIsNull":false,"IMAGE":"","IMAGEIsNull":true,"SCANSEE":"0","SCANSEEIsNull":false,"SCANSALE":"0","SCANSALEIsNull":false,"SSALEPWD":"","SSALEPWDIsNull":false,"NCREATEBY":1708,"NCREATEBYIsNull":false,"DCREATEDT":"2017-04-21T18:54:25","DCREATEDTIsNull":false,"NUPDATEBY":1708,"NUPDATEBYIsNull":false,"DUPDATEDT":"2017-04-21T18:54:25","DUPDATEDTIsNull":false,"SBUTTONTYPEIsNull":false,"SBUTTONTYPE":"0","ISSHOWDATE":0,"ISSHOWDATEIsNull":false,"DVALIDITYSTART":"0001-01-01T00:00:00","DVALIDITYSTARTIsNull":true,"DVALIDITYEND":"0001-01-01T00:00:00","DVALIDITYENDIsNull":true,"BARKCOLOR":"0,255,255,255","BARKCOLORIsNull":false,"FONTCOLOR":"0,0,0,0","FONTCOLORIsNull":false,"NSORT":0,"NSORTIsNull":false,"TICKET_EMPOWER":0,"TICKET_EMPOWERIsNull":false,"ISSHOWTIME":0,"SHOWBEGINTIME":"","SHOWENDTIME":"","ISSHOWWEEK":1,"SHOWWEEKMONDAY":1,"SHOWWEEKTUESDAY":0,"SHOWWEEKWEDNESDAY":1,"SHOWWEEKTHURSDAY":1,"SHOWWEEKFRIDAY":1,"SHOWWEEKSATURDAY":0,"SHOWWEEKSUNDAY":0,"HOLIDAYSID":0},"Inparcount":{"nTciektID":227,"nTciektIDIsNull":false,"nInParkCount":1,"nInParkCountIsNull":false},"Inparkinfo":[{"nTicketID":227,"nTicketIDIsNull":false,"nGzoneID":2555,"nGzoneIDIsNull":false,"maxCOUNT":1211,"maxCOUNTIsNull":false,"dayCount":1211,"dayCountIsNull":false},{"nTicketID":227,"nTicketIDIsNull":false,"nGzoneID":2640,"nGzoneIDIsNull":false,"maxCOUNT":100,"maxCOUNTIsNull":false,"dayCount":100,"dayCountIsNull":false},{"nTicketID":227,"nTicketIDIsNull":false,"nGzoneID":3467,"nGzoneIDIsNull":false,"maxCOUNT":1,"maxCOUNTIsNull":false,"dayCount":1,"dayCountIsNull":false}],"TIcketpark":[],"Ticketparkprice":[]}','0','227','2018/9/12 17:24:37','1708','','')
--------------- 以上為測驗資料 --------------
------ 1. 增加表值函式
IF OBJECT_ID('dbo.Fun_GetListFromJson') IS NOT NULL
DROP FUNCTION dbo.Fun_GetListFromJson
GO
-- =============================================
-- Author: yenange
-- Create date: 2018-09-16
-- Description: 從json獲取串列
-- =============================================
CREATE FUNCTION dbo.Fun_GetListFromJson
(
@s NVARCHAR(MAX), --完整json串
@parentNode NVARCHAR(200), --父級節點名稱
@subNode NVARCHAR(200) --希望取出的子節點
)
RETURNS
@t TABLE
(
item VARCHAR(200)
)
AS
BEGIN
DECLARE @d INT,@d2 INT
SET @d = CHARINDEX(@parentNode,@s)
IF @d>0
BEGIN
SET @s=SUBSTRING(@s,@d,LEN(@s))
WHILE CHARINDEX(@subNode+'"',@s)>0
BEGIN
SET @s=SUBSTRING(@s,CHARINDEX(@subNode+'"',@s),LEN(@s))
INSERT INTO @t(item)
SELECT rtrim(LTRIM(SUBSTRING(@s,CHARINDEX(':',@s)+1,CHARINDEX(',',@s)-CHARINDEX(':',@s)-1)))
SET @s=SUBSTRING(@s,CHARINDEX(',',@s),LEN(@s))
END
END
RETURN
END
GO
------2. 查詢、
--2.1
SELECT * FROM GS_T_Admission AS a
CROSS APPLY dbo.Fun_GetListFromJson(a.[JSON],'Inparkinfo','nGzoneID') AS f
--2.2
SELECT * FROM GS_T_Admission AS a
CROSS APPLY dbo.Fun_GetListFromJson(a.[JSON],'Inparkinfo','maxCOUNT') AS f2
算是實作了一定程度上的引數化。
如果還要其它的， 你自己再琢磨下吧。
談不上什么難度， 純粹是消耗耐心而已。
我的作業也比較忙， 你自己辛苦下
uj5u.com熱心網友回復：
Create FUNCTION [dbo].[parseJSON]( @JSON NVARCHAR(MAX))
RETURNS @hierarchy TABLE
(
element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
sequenceNo [int] NULL, /* the place in the sequence for the element */
parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
NAME NVARCHAR(2000),/* the name of the object */
StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */
ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/
)
AS
BEGIN
DECLARE
@FirstObject INT, --the index of the first open bracket found in the JSON string
@OpenDelimiter INT,--the index of the next open bracket found in the JSON string
@NextOpenDelimiter INT,--the index of subsequent open bracket found in the JSON string
@NextCloseDelimiter INT,--the index of subsequent close bracket found in the JSON string
@Type NVARCHAR(10),--whether it denotes an object or an array
@NextCloseDelimiterChar CHAR(1),--either a '}' or a ']'
@Contents NVARCHAR(MAX), --the unparsed contents of the bracketed expression
@Start INT, --index of the start of the token that you are parsing
@end INT,--index of the end of the token that you are parsing
@param INT,--the parameter at the end of the next Object/Array token
@EndOfName INT,--the index of the start of the parameter at end of Object/Array token
@token NVARCHAR(200),--either a string or object
@value NVARCHAR(MAX), -- the value as a string
@SequenceNo int, -- the sequence number within a list
@name NVARCHAR(200), --the name as a string
@parent_ID INT,--the next parent ID to allocate
@lenJSON INT,--the current length of the JSON String
@characters NCHAR(36),--used to convert hex to decimal
@result BIGINT,--the value of the hex symbol being parsed
@index SMALLINT,--used for parsing the hex value
@Escape INT --the index of the next escape character
DECLARE @Strings TABLE /* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped' in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in the JSON string by tokens representing the string */
(
String_ID INT IDENTITY(1, 1),
StringValue NVARCHAR(MAX)
)
SELECT--initialise the characters to convert hex to ascii
@characters='0123456789abcdefghijklmnopqrstuvwxyz',
@SequenceNo=0, --set the sequence no. to something sensible.
/* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */
@parent_ID=0;
WHILE 1=1 --forever until there is nothing more to do
BEGIN
SELECT
@start=PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin);--next delimited string
IF @start=0 BREAK --no more so drop through the WHILE loop
IF SUBSTRING(@json, @start+1, 1)='"'
BEGIN --Delimited Name
SET @start=@Start+1;
SET @end=PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin);
END
IF @end=0 --no end delimiter to last string
BREAK --no more
SELECT @token=SUBSTRING(@json, @start+1, @end-1)
--now put in the escaped control characters
SELECT @token=REPLACE(@token, FROMString, TOString)
FROM
(SELECT
'\"' AS FromString, '"' AS ToString
UNION ALL SELECT '\\', '\'
UNION ALL SELECT '\/', '/'
UNION ALL SELECT '\b', CHAR(08)
UNION ALL SELECT '\f', CHAR(12)
UNION ALL SELECT '\n', CHAR(10)
UNION ALL SELECT '\r', CHAR(13)
UNION ALL SELECT '\t', CHAR(09)
) substitutions
SELECT @result=0, @escape=1
--Begin to take out any hex escape codes
WHILE @escape>0
BEGIN
SELECT @index=0,
--find the next hex escape sequence
@escape=PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collate SQL_Latin1_General_CP850_Bin)
IF @escape>0 --if there is one
BEGIN
WHILE @index<4 --there are always four digits to a \x sequence
BEGIN
SELECT --determine its value
@result=@result+POWER(16, @index)
*(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1),
@characters)-1), @index=@index+1 ;
END
-- and replace the hex sequence by its unicode value
SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result))
END
END
--now store the string away
INSERT INTO @Strings (StringValue) SELECT @token
-- and replace the string with a token
SELECT @JSON=STUFF(@json, @start, @end+1,
'@string'+CONVERT(NVARCHAR(5), @@identity))
END
-- all strings are now removed. Now we find the first leaf.
WHILE 1=1 --forever until there is nothing more to do
BEGIN
SELECT @parent_ID=@parent_ID+1
--find the first object or list by looking for the open bracket
SELECT @FirstObject=PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin)--object or array
IF @FirstObject = 0 BREAK
IF (SUBSTRING(@json, @FirstObject, 1)='{')
SELECT @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@firstObject
WHILE 1=1 --find the innermost object or list...
BEGIN
SELECT
@lenJSON=LEN(@JSON+'|')-1
--find the matching close-delimiter proceeding after the open-delimiter
SELECT
@NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json,
@OpenDelimiter+1)
--is there an intervening open-delimiter of either type
SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%',
RIGHT(@json, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin)--object
IF @NextOpenDelimiter=0
BREAK
SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter
IF @NextCloseDelimiter<@NextOpenDelimiter
BREAK
IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{'
SELECT @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@NextOpenDelimiter
END
---and parse out the list or name/value pairs
SELECT
@contents=SUBSTRING(@json, @OpenDelimiter+1,
@NextCloseDelimiter-@OpenDelimiter-1)
SELECT
@JSON=STUFF(@json, @OpenDelimiter,
@NextCloseDelimiter-@OpenDelimiter+1,
'@'+@type+CONVERT(NVARCHAR(5), @parent_ID))
WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin))<>0
BEGIN
IF @Type='Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or null
BEGIN
SELECT
@SequenceNo=0,@end=CHARINDEX(':', ' '+@contents)--if there is anything, it will be a string-based name.
SELECT @start=PATINDEX('%[^A-Za-z@][@]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin)--AAAAAAAA
SELECT @token=SUBSTRING(' '+@contents, @start+1, @End-@Start-1),
@endofname=PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin),
@param=RIGHT(@token, LEN(@token)-@endofname+1)
SELECT
@token=LEFT(@token, @endofname-1),
@Contents=RIGHT(' '+@contents, LEN(' '+@contents+'|')-@end-1)
SELECT @name=stringvalue FROM @strings
WHERE string_id=@param --fetch the name
END
ELSE
uj5u.com熱心網友回復：
SELECT @Name=null,@SequenceNo=@SequenceNo+1
SELECT
@end=CHARINDEX(',', @contents)-- a string-token, object-token, list-token, number,boolean, or null
IF @end=0
--HR Engineering notation bugfix start
IF ISNUMERIC(@contents) = 1
SELECT @end = LEN(@contents)
Else
--HR Engineering notation bugfix end
SELECT @end=PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @contents+' ' collate SQL_Latin1_General_CP850_Bin) + 1
SELECT
@start=PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin)
--select @start,@end, LEN(@contents+'|'), @contents
SELECT
@Value=https://bbs.csdn.net/topics/RTRIM(SUBSTRING(@contents, @start, @End-@Start)),
@Contents=RIGHT(@contents+' ', LEN(@contents+'|')-@end)
IF SUBSTRING(@value, 1, 7)='@object'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 8, 5),
SUBSTRING(@value, 8, 5), 'object'
ELSE
IF SUBSTRING(@value, 1, 6)='@array'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 7, 5),
SUBSTRING(@value, 7, 5), 'array'
ELSE
IF SUBSTRING(@value, 1, 7)='@string'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, stringvalue, 'string'
FROM @strings
WHERE string_id=SUBSTRING(@value, 8, 5)
ELSE
IF @value IN ('true', 'false')
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'boolean'
ELSE
IF @value='https://bbs.csdn.net/topics/null'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'null'
ELSE
IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin)>0
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'real'
ELSE
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'int'
if @Contents=' ' Select @SequenceNo=0
END
END
INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT '-',1, NULL, '', @parent_id-1, @type
--
RETURN
END
sql太長，一次發不出來，你把這一部分和上面一部分合并在一起，就是一個表值函式，很通用的，決議json用起來還是很爽的
SELECT b.stringValue FROM GS_T_Admission AS a
CROSS APPLY Crawler.dbo.parseJSON(a.JSON) AS b
WHERE a.NTICKETID =227 AND b.NAME='NGZONEID'
uj5u.com熱心網友回復：
雨亭這個還是不錯的，
uj5u.com熱心網友回復：
贊,使用很好,感謝各位大佬!uj5u.com熱心網友回復：
用sql server 2016的json功能要簡單一些轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/17927.html
標籤：疑難問題