How I can get the first 3 digits in 123456 Numbers in sql?

Question

I have field called CallingParty in My CDR table it contains data like this:

CallingParty
------------
267672668788

I want to select the first 3 number of each of those numbers like

CallingParty
------------
267

Guttersnipe

possible duplicate of SQL: how to get the left 3 numbers from an int – Pegu

Does this answer your question? SQL: how to get the left 3 numbers from an int – Homogenous

Answer 1

if CallingParty is of type int:

SELECT CAST(LEFT(CallingParty, 3) AS INT)
From CDR

Pod

No need to CAST explicitly, LEFT(123456,3) will do an implicit cast anyway. – Cusec

That wasn't the cast I meant! Your edit has broken your answer as now you have a dangling AS INT I meant the cast to varchar as input to the LEFT function. Some people might prefer the explicit cast anyway but I find it more readable/less noisy without. – Cusec

Answer 2

SQL Server has a Left() function, but it works best on strings. (varchar/char in SQL)

Select left(cast(267672668788 as varchar), 3)

Vanhook

left is more readable, and less confusing especially when some systems start at 0 and some at 1. KISS – Render

Answer 3

Use this query:

SELECT SUBSTRING(CAST(CallingParty AS VARCHAR(50)), 1, 3) FROM [CDR]

Schade

Answer 4

If the data length does not change then you can always divide by 10 * the digits you have

SELECT FLOOR(267672668788 / 1000000000)
=267

Salvadorsalvadore

Answer 5

Try this:

SELECT Substring(callingparty, 1, Length(callingparty) - 9) 
FROM   cdr; 

Patman