在SQL Server中使用STRING函数 [英] Working with STRING functions in SQL server

问题描述

嗨伙计!!



我有一个列'EmployeeAddress'。我想从地址栏下面获得唯一的员工'门牌号'。



employeeAddress

---------------------

245 1st main，Silkboard，Bangalore-560068 ------这里'245'是门牌号码

243 BTM，班加罗尔-560068

653 Koramangala，班加罗尔 - 560079

8 12th main，1st cross，Adugodi，Bangalore

1电子城，班加罗尔------------------这里'1'是门牌号码

5电子城，班加罗尔------------------这里'5'是门牌号码

8电子城，班加罗尔------------------这里'8'是门牌号码





有谁可以帮我这个？在此先感谢..



我尝试过：



我尝试如下。



SELECT LEFT（employeeAddress，charindex（''，employeeAddress）-1）FROM表



但我收到以下错误



消息537，等级16，状态2，行1

无效长度参数传递给LEFT或SUBSTRING函数。

Hi Folks!!

I have a column 'EmployeeAddress'.I want to get the only employee 'House Number' from below address column.

employeeAddress
---------------------
245 1st main,Silkboard,Bangalore-560068------Here '245' is House Number
243 BTM,Bangalore-560068
653 Koramangala,Bangalore-560079
8 12th main,1st cross,Adugodi,Bangalore
1 electronic city,Bangalore------------------Here '1' is House number
5 electronic city,Bangalore------------------Here '5' is House number
8 electronic city,Bangalore------------------Here '8' is House number


Can anyone help me on this? Thanks in Advance..

What I have tried:

I tried like below .

SELECT LEFT(employeeAddress,charindex(' ',employeeAddress)-1) FROM Table

But i am getting below error

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

推荐答案

首先，这不是存储数据的方法。如果您需要表中的门牌号码，则必须将其存储为门牌号码。但是，如果您无法做到这一点，您将不得不依赖于从您的地址字段中查找子字符串。现在，问题是我们将获得''的第一个索引并找到子字符串。但是，如果地址有前面的空格怎么办？您必须修剪字符串左侧的空格。在推进之前，我建议你为SQL Server谷歌'SUBSTRING'和'LTRIM'功能。然后只尝试此查询以便您理解：

First of all this is not the way to store data. If you need 'House Number' from your table you have to store it as 'House Number'. Still, if you are unable to do that you will have to rely on finding the substring from your address field. Now, the problem is we will get the first index of ' ' and find the substring. But what if the address has preceding spaces? You will have to trim the blank spaces from the left of your string. I would recommend you to google 'SUBSTRING' and 'LTRIM' functions for SQL Server before moving ahead. Then only try this query for your understanding:

DECLARE @address VARCHAR(50) = '   2508 Sample Address'
SELECT SUBSTRING(LTRIM(@address),1, CHARINDEX(' ', LTRIM(@address)))

现在，根据您的要求，您应该写：

Now, for your requirement, you should write:

SELECT SUBSTRING(LTRIM(employeeAddress),1, CHARINDEX(' ', LTRIM(employeeAddress))) AS [House Number] FROM [YourTableName]

写简单查询：

Write simple query:

SELECT SUBSTRING(employeeAddress, 1, CHARINDEX(' ', employeeAddress, 1) - 1) FROM Table

这篇关于在SQL Server中使用STRING函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！