8

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
peterh
  • 11,875
  • 18
  • 85
  • 108
Cabaas Cabdi
  • 99
  • 1
  • 1
  • 4
  • possible duplicate of [SQL: how to get the left 3 numbers from an int](http://stackoverflow.com/questions/2640048/sql-how-to-get-the-left-3-numbers-from-an-int) – Zsolt Botykai Jun 01 '12 at 07:05
  • Does this answer your question? [SQL: how to get the left 3 numbers from an int](https://stackoverflow.com/questions/2640048/sql-how-to-get-the-left-3-numbers-from-an-int) – peterh Dec 23 '20 at 00:47

5 Answers5

14

if CallingParty is of type int:

SELECT CAST(LEFT(CallingParty, 3) AS INT)
From CDR
Habib
  • 219,104
  • 29
  • 407
  • 436
  • 4
    No need to `CAST` explicitly, `LEFT(123456,3)` will do an implicit cast anyway. – Martin Smith May 31 '12 at 07:07
  • 1
    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. – Martin Smith May 31 '12 at 07:11
2

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

Select left(cast(267672668788 as varchar), 3)
dbrosier
  • 330
  • 4
  • 11
  • left is more readable, and less confusing especially when some systems start at 0 and some at 1. KISS – DaFi4 Apr 07 '17 at 15:31
1

Use this query:

SELECT SUBSTRING(CAST(CallingParty AS VARCHAR(50)), 1, 3) FROM [CDR]
Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
Nick
  • 1,128
  • 7
  • 12
1

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

SELECT FLOOR(267672668788 / 1000000000)
=267
vfrank66
  • 1,318
  • 19
  • 28
-1

Try this:

SELECT Substring(callingparty, 1, Length(callingparty) - 9) 
FROM   cdr;
WhatsThePoint
  • 3,395
  • 8
  • 31
  • 53
Venkat Reddy
  • 1