Learn how to a build a cloud-first strategyRegister Now

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

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

Thank you in advance
0
hmra
Asked:
hmra
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select left(yourfield, charindex(' ', yourfield)-1 ) from yourtable
0
 
Imran Javed ZiaCommented:
Hi,
Please try following

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

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

Open in new window


Thanks
0
 
Alpesh PatelAssistant ConsultantCommented:
Select SUBSTRING('123 Dr. Perez Perez',CHARINDEX(' ','123 Dr. Perez Perez')+1,len('123 Dr. Perez Perez'))
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
MadRacer1Commented:
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]


I think this will help you..
0
 
Ephraim WangoyaCommented:


select case
         when charindex(' ', Field1) > 0 then
           left(Field1, charindex(' ', Field1)-1 )
         else
           0
         end Field1
from Table1
0
 
planoczCommented:
your you doing this on the report or in the sql statement?
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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