Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 345

# Ge numbers before te first space sql

Hello there,

How can I get the number before the first space?

123 Dr. Perez Perez
14567 Miami City

This should be the result

123
14567

0
hmra
1 Solution

Billing EngineerCommented:
select left(yourfield, charindex(' ', yourfield)-1 ) from yourtable
0

Commented:
Hi,

``````declare @txt as varchar(100)
set @txt = '123 Dr. Perez Perez'
--set @txt = '14567 Miami City'

select Substring(@txt, 1, CHARINDEX (' ', @txt)-1)
``````

Thanks
0

Assistant ConsultantCommented:
Select SUBSTRING('123 Dr. Perez Perez',CHARINDEX(' ','123 Dr. Perez Perez')+1,len('123 Dr. Perez Perez'))
0

Commented:
HI HRMA,
you can do it with various ways.
i think it can be done with three ways.
1.  Using SUBSTRING function
2.  Using LEFT Function
3.  Using PARSENAME function

However in PARSENAME function you have to take care of few things.
solution is as mentioned below :

DECLARE @Employee AS VARCHAR(100)
SET @Employee = '123 Dr. Perez Perez'
--      SET @Employee = '14567 Miami City'

1.  Using SUBSTRING function
SELECT SUBSTRING(@Employee, 1, CHARINDEX (' ', @Employee)-1)

2.  Using LEFT Function

SELECT LEFT(@Employee, CHARINDEX (' ', @Employee)-1)

3.  Using PARSENAME function
DECLARE @FullName        VARCHAR(100)
SET @FullName = '14567 Miami City'

SELECT
PARSENAME(REPLACE(@FullName, ' ', '.'), 3) AS [Employee Code]
,PARSENAME(REPLACE(@FullName, ' ', '.'), 2) AS [FirstName]
,PARSENAME(REPLACE(@FullName, ' ', '.'), 1) AS [LastName]

0

Commented:

select case
when charindex(' ', Field1) > 0 then
left(Field1, charindex(' ', Field1)-1 )
else
0
end Field1
from Table1
0

Commented:
your you doing this on the report or in the sql statement?
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.