替换SQL中的字符 [英] Replace character in SQL

问题描述

大家好，

我想从以下字符串中删除'（City： - Mumbai）'。在这个城市名称是动态字段，它可以是斋浦尔，德里，阿杰梅尔等使用SQL查询。



字符串是： -

'地址：Nehru Road Ext。，Vile Parle（东）

近孟买国内机场 - 400099.

马哈拉施特拉邦。印度。 （城市： - 孟买）

电话：不可用'



请尽快帮助我

Hello everyone,
I want to remove '( City :- Mumbai )' from following string. In this City name is dynamically field it can be Jaipur, Del Ajmer etc. using sql query.

The string is:-
'Address : Nehru Road Ext., Vile Parle (East)
Near Domestic Airport, Mumbai – 400099.
Maharashtra. INDIA. ( City :- Mumbai )
Tel : Not Available'

Plese help me as soon as possible

推荐答案

REPLACE（T-SQL） [ ^ ]或 SUBSTRING [ ^ ]和 PATINDEX [< a href =http://msdn.microsoft.com/en-us/library/ms188395.aspxtarget =_ blanktitle =新窗口> ^ ]正是您所需要的。



主要想法是：

REPLACE (T-SQL)[^] or SUBSTRING[^] together with PATINDEX[^] is what you're looking for.

The main idea is:

SELECT REPLACE(FieldName, SUBSTRING(FieldName, PStart, LEN(FieldName)-PEnd),'') AS NewFieldValues
FROM (
    SELECT FieldName, PATINDEX('%(City:%',FieldName) AS PStart, PATINDEX('%)%',FieldName) AS PEnd 
    FROM TableName
    WHERE PATINDEX('%(City:%',FieldName)>0
) AS T

您没有提供足够的信息来帮助您指导。



'工作'样本：

You did not provide enough information to help you direct.

'Working' sample:

DECLARE @tmp TABLE (SomeString VARCHAR(255))

INSERT INTO @tmp (SomeString)
VALUES('Address : Nehru Road Ext., Vile Parle (East)
Near Domestic Airport, Mumbai – 400099.
Maharashtra. INDIA. ( City :- Mumbai )
Tel : Not Available'),
('Address : Bahru Road Ext., Vile Parle (West)
Near Domestic Airport, Calcuta – 422099.
Maharashtra. INDIA. ( City :- Calcuta )
Tel : UN')

SELECT REPLACE(SomeString, SUBSTRING(SomeString, PStart, PEnd-1-Pstart),'') AS NewFieldValues
FROM (
    SELECT SomeString, PATINDEX('%( City%',SomeString) AS PStart, PATINDEX('%Tel%',SomeString) AS PEnd 
    FROM @tmp
    WHERE PATINDEX('%( City%',SomeString)>0
) AS T

结果：

Result:

Address : Nehru Road Ext., Vile Parle (East)
Near Domestic Airport, Mumbai – 400099.
Maharashtra. INDIA. 
Tel : Not Available

Address : Bahru Road Ext., Vile Parle (West)
Near Domestic Airport, Calcuta – 422099.
Maharashtra. INDIA. 
Tel : UN

阅读本文： http://stackoverflow.com/questions/814548/how-to-replace-a-string-in-a-sql-server-table-栏 [ ^ ]

这篇关于替换SQL中的字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！