---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-12-21 20:25:17 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(26)) insert [tb] select '千柏鼻炎片/100片' union all select '辛芩颗粒/20g@8代' union all select '肺力咳合剂/100ml' union all select '复方草珊瑚含片/48片' union all select '儿童咳液/10ml@10支' union all select '小儿咳喘灵口服液/10ml@10支' union all select '桂龙咳喘宁胶囊/0.3g@60粒' union all select '急支糖浆/200ml' union all select '气管炎丸/300粒' union all select '京制咳嗽痰喘丸/180粒' --------------开始查询-------------------------- select substring(col,1,charindex('/',col)-1) from tb ----------------结果---------------------------- /* -------------------------- 千柏鼻炎片 辛芩颗粒 肺力咳合剂 复方草珊瑚含片 儿童咳液 小儿咳喘灵口服液 桂龙咳喘宁胶囊 急支糖浆 气管炎丸 京制咳嗽痰喘丸(10 行受影响)*/
declare @s varchar(50) set @s = '千柏鼻炎片/100片' select left(@s,charindex('/',@s)-1) as col col -------------------------------------------------- 千柏鼻炎片(1 行受影响)
right(data,charindex('/',reverse(data+'/'))-1)
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-12-21 20:25:17 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(26)) insert [tb] select '千柏鼻炎片/100片' union all select '辛芩颗粒/20g@8代' union all select '肺力咳合剂/100ml' union all select '复方草珊瑚含片/48片' union all select '儿童咳液/10ml@10支' union all select '小儿咳喘灵口服液/10ml@10支' union all select '桂龙咳喘宁胶囊/0.3g@60粒' union all select '急支糖浆/200ml' union all select '气管炎丸/300粒' union all select '京制咳嗽痰喘丸/180粒' --------------开始查询-------------------------- select substring(col,1,charindex('/',col)-1) as col1, substring(col,charindex('/',col)+1,len(col)-charindex('/',col)) as col2 from tb ----------------结果---------------------------- /* col1 col2 -------------------------- -------------------------- 千柏鼻炎片 100片 辛芩颗粒 20g@8代 肺力咳合剂 100ml 复方草珊瑚含片 48片 儿童咳液 10ml@10支 小儿咳喘灵口服液 10ml@10支 桂龙咳喘宁胶囊 0.3g@60粒 急支糖浆 200ml 气管炎丸 300粒 京制咳嗽痰喘丸 180粒(10 行受影响) */
right(data,charindex('/',reverse('/'+data))-1)
select left(col,charindex('/',col)-1),substring(col,charindex('/',col)+1,1000) from xxxx
2> select left(col,charindex('/',col)-1),substring(col,charindex('/',col)+1,1000) 3> from ( 4> select '千柏鼻炎片/100片' as col union all 5> select '辛芩颗粒/20g@8代' union all 6> select '肺力咳合剂/100ml' union all 7> select '复方草珊瑚含片/48片' union all 8> select '儿童咳液/10ml@10支' union all 9> select '小儿咳喘灵口服液/10ml@10支' union all 10> select '桂龙咳喘宁胶囊/0.3g@60粒' union all 11> select '急支糖浆/200ml' union all 12> select '气管炎丸/300粒' union all 13> select '京制咳嗽痰喘丸/180粒' 14> ) t 15> go | --------------------------|-------------------------- 千柏鼻炎片 |100片 辛芩颗粒 |20g@8代 肺力咳合剂 |100ml 复方草珊瑚含片 |48片 儿童咳液 |10ml@10支 小儿咳喘灵口服液 |10ml@10支 桂龙咳喘宁胶囊 |0.3g@60粒 急支糖浆 |200ml 气管炎丸 |300粒 京制咳嗽痰喘丸 |180粒(10 rows affected) 1>
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-21 20:25:17
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(26))
insert [tb]
select '千柏鼻炎片/100片' union all
select '辛芩颗粒/20g@8代' union all
select '肺力咳合剂/100ml' union all
select '复方草珊瑚含片/48片' union all
select '儿童咳液/10ml@10支' union all
select '小儿咳喘灵口服液/10ml@10支' union all
select '桂龙咳喘宁胶囊/0.3g@60粒' union all
select '急支糖浆/200ml' union all
select '气管炎丸/300粒' union all
select '京制咳嗽痰喘丸/180粒'
--------------开始查询--------------------------
select substring(col,1,charindex('/',col)-1) from tb
----------------结果----------------------------
/* --------------------------
千柏鼻炎片
辛芩颗粒
肺力咳合剂
复方草珊瑚含片
儿童咳液
小儿咳喘灵口服液
桂龙咳喘宁胶囊
急支糖浆
气管炎丸
京制咳嗽痰喘丸(10 行受影响)*/
declare @s varchar(50)
set @s = '千柏鼻炎片/100片'
select left(@s,charindex('/',@s)-1) as col
col
--------------------------------------------------
千柏鼻炎片(1 行受影响)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-21 20:25:17
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(26))
insert [tb]
select '千柏鼻炎片/100片' union all
select '辛芩颗粒/20g@8代' union all
select '肺力咳合剂/100ml' union all
select '复方草珊瑚含片/48片' union all
select '儿童咳液/10ml@10支' union all
select '小儿咳喘灵口服液/10ml@10支' union all
select '桂龙咳喘宁胶囊/0.3g@60粒' union all
select '急支糖浆/200ml' union all
select '气管炎丸/300粒' union all
select '京制咳嗽痰喘丸/180粒'
--------------开始查询--------------------------
select
substring(col,1,charindex('/',col)-1) as col1,
substring(col,charindex('/',col)+1,len(col)-charindex('/',col)) as col2
from tb
----------------结果----------------------------
/* col1 col2
-------------------------- --------------------------
千柏鼻炎片 100片
辛芩颗粒 20g@8代
肺力咳合剂 100ml
复方草珊瑚含片 48片
儿童咳液 10ml@10支
小儿咳喘灵口服液 10ml@10支
桂龙咳喘宁胶囊 0.3g@60粒
急支糖浆 200ml
气管炎丸 300粒
京制咳嗽痰喘丸 180粒(10 行受影响)
*/
right(data,charindex('/',reverse('/'+data))-1)
from xxxx
3> from (
4> select '千柏鼻炎片/100片' as col union all
5> select '辛芩颗粒/20g@8代' union all
6> select '肺力咳合剂/100ml' union all
7> select '复方草珊瑚含片/48片' union all
8> select '儿童咳液/10ml@10支' union all
9> select '小儿咳喘灵口服液/10ml@10支' union all
10> select '桂龙咳喘宁胶囊/0.3g@60粒' union all
11> select '急支糖浆/200ml' union all
12> select '气管炎丸/300粒' union all
13> select '京制咳嗽痰喘丸/180粒'
14> ) t
15> go
|
--------------------------|--------------------------
千柏鼻炎片 |100片
辛芩颗粒 |20g@8代
肺力咳合剂 |100ml
复方草珊瑚含片 |48片
儿童咳液 |10ml@10支
小儿咳喘灵口服液 |10ml@10支
桂龙咳喘宁胶囊 |0.3g@60粒
急支糖浆 |200ml
气管炎丸 |300粒
京制咳嗽痰喘丸 |180粒(10 rows affected)
1>
col1 col2
-------------------------- -------------------
千柏鼻炎片 100片
辛芩颗粒 20g@8代
肺力咳合剂 100ml
复方草珊瑚含片 48片
儿童咳液 10ml@10支
小儿咳喘灵口服液 10ml@10支
桂龙咳喘宁胶囊 0.3g@60粒
急支糖浆 200ml
气管炎丸 300粒
京制咳嗽痰喘丸 180粒(10 行受影响)
*/