如何基于()在sql中拆分查询 [英] How to split query in sql based on ()
问题描述
如何拆分查询以便我获取新行中的数据..例如
美国邮件(9.50)
Class Mail(加拿大)(15.00)
航空邮件(国际地址)(26.00)等...
这里是我的查询尝试使用|它没有用。任何人都可以给我查询
从ApplicationSettings中选择SettingValue,其中SettingValue ='美国邮件(9.50) )|头等舱邮件(加拿大)(15.00)|航空邮件(国际地址)(26.00)| +次日航空(16.00 + 28.66 = 44.66)| UPS第2天(11.00 + 17.44 = 28.44)| UPS地面(10 + 9.29) = 19.29)| UPS NEXT DAY AIR(10.00 + 17.44 = 27.44)'
How to split query so that i get the data in new lines..such as
US Mail (9.50)
First Class Mail (Canada) (15.00)
Airmail (International Addresses) (26.00) etc...
here is the query which i tried using | it does'nt work..Can anyone pls give me the query
select SettingValue from ApplicationSettings where SettingValue='US Mail (9.50)|First Class Mail (Canada) (15.00)|Airmail (International Addresses) (26.00)|+Next Day Air (16.00 + 28.66 = 44.66)|UPS 2ND Day (11.00 + 17.44 = 28.44)|UPS Ground (10 + 9.29 = 19.29)|UPS NEXT DAY AIR (10.00 + 17.44 = 27.44)'
推荐答案
你可以创建一个功能来删除它,只需
you can create a fuction to remove this ,just
create FUNCTION [FN_REMOVE_SPECIAL_CHARACTER] (
@INPUT_STRING varchar(300))
RETURNS VARCHAR(300)
AS
BEGIN
--declare @testString varchar(100),
DECLARE @NEWSTRING VARCHAR(100)
-- set @teststring = '@san?poojari(darsh)'
SET @NEWSTRING = @INPUT_STRING ;
With SPECIAL_CHARACTER as
(
SELECT '|' as item
)
SELECT @NEWSTRING = Replace(@NEWSTRING, ITEM, '') FROM SPECIAL_CHARACTER
return @NEWSTRING
END
运行此功能你的查询窗口
并传递你的字符串。
run this Fuction on your query window
and pass your string.
select dbo.[FN_REMOVE_SPECIAL_CHARACTER] ('US Mail (9.50)|First Class Mail (Canada) (15.00)|Airmail (International Addresses) (26.00)|+Next Day Air (16.00 + 28.66 = 44.66)|UPS 2ND Day (11.00 + 17.44 = 28.44)|UPS Ground (10 + 9.29 = 19.29)|UPS NEXT DAY AIR (10.00 + 17.44 = 27.44)')
这篇关于如何基于()在sql中拆分查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!