如何拆分具有特殊字符的列数据。 [英] How do I split the column data which are having special characters.

问题描述

我有一个列名City，其数据包含spl字符和空格。

Ex.:My Table

1.艾哈迈达巴德 - CG路

2.艾哈迈达巴德 - 卫星路

2.德里 - Malviya Nagar

3。海德拉巴 - Kukatpally



我想根据我的输出检索数据。

OutPut：

1. col1 = Ahmedab​​ad

col2 = CG Road

2. col1 = Ahmedab​​ad

col2 =卫星路
3. col1 =德里

col2 = Malviya Nagar

4. col1 =海德拉巴

col2 = Kukatpally



请在我的查询中帮助我。

这是我的查询，我试图工作，但无法按要求显示。



CASE

WHEN城市像'％ - ％'那么左（City，Charindex（''，City） - 1）ELSE City

END as CityName，

CASE

当城市喜欢'％ - ％'时（城市，Charindex（''，反向（城市））-1）

END作为CityLocation





-

在此先感谢：）

Hi,
I have a column name City whose data is containing spl characters and space.
Ex.:My Table
1. Ahmedabad - CG Road
2. Ahmedabad-Satellite Road
2. Delhi - Malviya Nagar
3. Hyderabad - Kukatpally

I want to retrieve the data as per my output.
OutPut:
1. col1 =Ahmedabad
col2 = CG Road
2. col1 =Ahmedabad
col2 = Satellite Road
3. col1 =Delhi
col2 = Malviya Nagar
4. col1 =Hyderabad
col2 = Kukatpally

Please help me out in my query.
Here is my query which i tried to work but unable to display as required.

CASE
WHEN City LIKE '%-%' THEN LEFT(City, Charindex(' ', City) - 1) ELSE City
END as CityName,
CASE
WHEN City LIKE '%-%' THEN RIGHT(City, Charindex(' ', Reverse(City)) -1)
END as CityLocation


--
Thanks in Advance :)

推荐答案

请运行这个SQL查询，这肯定会给你所需的输出。



提前谢谢

Hi, please run this sql query , this will gives you the required output surely.

Thanks in advance

SELECT substring(City, 1, CHARINDEX('-',City)-1) CityName,
substring(City, CHARINDEX('-',City)+1, LEN(City)) CityLocation
FROM My_Table

更改''to' - '：

Change the ' ' to '-':

SELECT
CASE WHEN City LIKE '%-%' THEN LEFT(City, Charindex('-', City) - 1) ELSE City END as CityName,
CASE WHEN City LIKE '%-%' THEN RIGHT(City, Charindex('-', Reverse(City)) -1) END as CityLocation
FROM Addresses

hi，



使用这个

Use this one

SELECT city,
       Substring(city, 1, Charindex('-', city, 1) - 1)     AS col1,
       Substring(city, Charindex('-', city, 1)+1, Len(city)) AS col2
FROM   cp_locations_D_D

谢谢

Dhamu

Thanks
Dhamu

这篇关于如何拆分具有特殊字符的列数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！