请教，查询并且显示的问题
假设：TableA
有以下属性
date
NO
memo
date NO memo
2012-01-01 1 connect from 10.101.1.1 from NO address: 192.168.1.1
2012-01-01 2 connect from 10.101.1.2 from NO address: 192.168.1.2
2012-01-01 3 connect from 10.101.1.3 from NO address: 192.168.1.3
如果我写
select from TableA where memo like '%10.101.1.1%'
那么得到的结果就是
date NO memo
2012-01-01 1 connect from 10.101.1.1 from NO address: 192.168.1.1
我想知道如果我想得到的结果是
date NO memo
2012-01-01 1 10.101.1.1
我的这个SQL该如何去写？
[解决办法]
select date,NO,'10.101.1.1' from TableA where memo like '%10.101.1.1%'
[解决办法]
substring 函数截取
[解决办法]
select date,NO,stuff(left(memo,patindex('% from NO%',memo)),1,patindex('%[0-9]%',left(memo,patindex('% from NO%',memo)))-1,'') [memo] from TableA where memo like '%10.101.1.1%'
[解决办法]
select date,no,substring(memo,charindex('from',memo)+5,charindex('from NO',memo)-charindex('from',memo)-5) as memo
from TableA where memo like '%10.101.1.1%'
[解决办法]
如果你的数据格式固定的话，下面语句可以查找两个from之间的结果，那个5是from这四个字母加上一个空格
DECLARE @a VARCHAR(128)
SET @a='connect from 10.101.1.1 from NO address: 192.168.1.1 '
SELECT SUBSTRING(@a,PATINDEX('%from %',@a)+5,PATINDEX('% from NO%',@a)-PATINDEX('%from %',@a)-5)
/*
--------------------------------------------------------
10.101.1.1
*/
