想把数据库里以a开头，或者A，或者首字拼音为A的提取出来，语句怎么写，，急呀!!

[ptxiaolinzi886]

想把数据库里以a开头，或者A，或者首字拼音为A的提取出来，语句怎么写,
类似b,c,d,...等等
，，急呀!!

[ptxiaolinzi886]

我试了，都不行呀

[wuyq11]

select * from TableName where contains(列名(, 列名), 'b*' and 'c*') 检索表中包含a,b,c之一的所有列

[jianshao810]

Select * From TableName Where FieldName like 'A%'

[huangwenquan123]

[Quote=引用 9 楼 koukoujiayi 的回复:]
也可以
select * from 表 where ASCII(字段) in (65,97,176)
[/Quote]学习了 还可以这样

[ptxiaolinzi886]

用select * from 表 where ASCII(字段) in (65,97,176)

第一个字为 包 的怎么也被搜索出来

[sy_binbin]

用SQL的自定义函数
-- =============================================
-- Author: xoyozo
-- Create date: 2010-4-17
-- Description: 提供中文首字母
-- =============================================
CREATE FUNCTION dbo.fun_getPY
(
@str NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @word NCHAR(1),@PY NVARCHAR(4000)
SET @PY=''
WHILE len(@str)>0
BEGIN
SET @word=left(@str,1)
SET @PY=@PY+(CASE WHEN unicode(@word) BETWEEN 19968 AND 19968+20901
THEN (SELECT TOP 1 PY FROM (
SELECT 'A' AS PY,N'驁' AS word
UNION ALL SELECT 'B',N'簿'
UNION ALL SELECT 'C',N'錯'
UNION ALL SELECT 'D',N'鵽'
UNION ALL SELECT 'E',N'樲'
UNION ALL SELECT 'F',N'鰒'
UNION ALL SELECT 'G',N'腂'
UNION ALL SELECT 'H',N'夻'
UNION ALL SELECT 'J',N'攈'
UNION ALL SELECT 'K',N'穒'
UNION ALL SELECT 'L',N'鱳'
UNION ALL SELECT 'M',N'旀'
UNION ALL SELECT 'N',N'桛'
UNION ALL SELECT 'O',N'漚'
UNION ALL SELECT 'P',N'曝'
UNION ALL SELECT 'Q',N'囕'
UNION ALL SELECT 'R',N'鶸'
UNION ALL SELECT 'S',N'蜶'
UNION ALL SELECT 'T',N'籜'
UNION ALL SELECT 'W',N'鶩'
UNION ALL SELECT 'X',N'鑂'
UNION ALL SELECT 'Y',N'韻'
UNION ALL SELECT 'Z',N'咗'
) T
WHERE word>=@word COLLATE Chinese_PRC_CS_AS_KS_WS
ORDER BY PY ASC) ELSE @word END)
SET @str=right(@str,len(@str)-1)
END
RETURN @PY
END

[天下如山]

用SQL自带函数patindex或者CHARINDEX去搜索

如果得到返回的是1则select

patindex可以匹配通配符(正则)。

就不举例子啦。

[koukoujiayi]

[Quote=引用 14 楼 ptxiaolinzi886 的回复:]
用select * from 表 where ASCII(字段) in (65,97,176)

第一个字为 包 的怎么也被搜索出来
[/Quote]
试试
select * from 表 where ASCII(left(字段,1)) in (65,97,176)

[ptxiaolinzi886]

还是一样呀，什么 百 柏 包 都可以被搜索出来

[ptxiaolinzi886]

包的ASCII为176 晕

[csdnflys]

[Quote=引用 12 楼 jianshao810 的回复:]
Select * From TableName Where FieldName like 'A%'
[/Quote]
这个不可以吗？？

[yufenghua]

[Quote=引用 7 楼 koukoujiayi 的回复:]
ascii码176是a开头的汉字,
select * from 表 where Lower(字段) like 'a%' or ASCII(字段)=176
[/Quote]
还可以通过ASCII码的，顶一下，学习了。。。。

[kokorenfeng]

Select * From 表名Where (xx like 'A%' or xx like 'a%')

[种草德鲁伊]

急了就去厕所，别忍着。

[cjfriends]

强呀，学习了

[dengxiao1981]

顶。。。。。。。

[打一壶酱油]

SIGetCode.dll 是一个可以获取汉字拼音或者五笔编码的动态库，添加到你的工程就并copy demo代码过去就可以了，曾经用过，确实可行

[kkbac]

为所有的数据增加一个拼音字段.

[种草德鲁伊]

[Quote=引用 25 楼 kkbac 的回复:]

为所有的数据增加一个拼音字段.
[/Quote]

这样确实比较好，查询效率比较高。

[minitoy]

函数那个好像不可行.

SQL> SELECT  min(py) FROM (
  2  SELECT 'A' AS PY,    N'驁' AS word  from dual
  3  UNION ALL SELECT 'B',N'簿'    from dual
  4  UNION ALL SELECT 'C',N'錯'    from dual
  5  UNION ALL SELECT 'D',N'鵽'    from dual
  6  UNION ALL SELECT 'E',N'樲'    from dual
  7  UNION ALL SELECT 'F',N'鰒'    from dual
  8  UNION ALL SELECT 'G',N'腂'    from dual
  9  UNION ALL SELECT 'H',N'夻'    from dual
 10  UNION ALL SELECT 'J',N'攈'    from dual
 11  UNION ALL SELECT 'K',N'穒'    from dual
 12  UNION ALL SELECT 'L',N'鱳'    from dual
 13  UNION ALL SELECT 'M',N'旀'    from dual
 14  UNION ALL SELECT 'N',N'桛'    from dual
 15  UNION ALL SELECT 'O',N'漚'    from dual
 16  UNION ALL SELECT 'P',N'曝'    from dual
 17  UNION ALL SELECT 'Q',N'囕'    from dual
 18  UNION ALL SELECT 'R',N'鶸'    from dual
 19  UNION ALL SELECT 'S',N'蜶'    from dual
 20  UNION ALL SELECT 'T',N'籜'    from dual
 21  UNION ALL SELECT 'W',N'鶩'    from dual
 22  UNION ALL SELECT 'X',N'鑂'    from dual
 23  UNION ALL SELECT 'Y',N'韻'    from dual
 24  UNION ALL SELECT 'Z',N'咗'    from dual
 25  ) T
 26  WHERE word>='吧'
 27  ;
 
MIN(PY)
-------
A
 
SQL>