如何基于（）在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屋！