362

I have a SQL column with a length of 6. Now want to take only the first char of that column. Is there any string function in SQL to do this?

slartidan
  • 20,403
  • 15
  • 83
  • 131
Vinod
  • 31,933
  • 35
  • 96
  • 119

8 Answers8

589

LEFT(colName, 1) will also do this, also. It's equivalent to SUBSTRING(colName, 1, 1).

I like LEFT, since I find it a bit cleaner, but really, there's no difference either way.

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
Eric
  • 92,005
  • 12
  • 114
  • 115
  • 3
    I don't know about SQL server, but logically a database server may be able to optimise LEFT better than SUBSTRING when it is using an index. – thomasrutter Apr 27 '09 at 05:26
  • 27
    @thomasrutter, Looking at an execution plan, SQL Server (at least 2008R2) internally translates `LEFT(colName, length)` into `SUBSTRING(colName, 1, length)`. So **there is no any optimizations** here, it's just a preference. – Alexander Abakumov Sep 15 '14 at 15:15
  • LEFT not works with Firebird 1.5 – Mmx Dec 06 '21 at 21:41
69

I prefer:

SUBSTRING (my_column, 1, 1)

because it is Standard SQL-92 syntax and therefore more portable.


Strictly speaking, the standard version would be

SUBSTRING (my_column FROM 1 FOR 1)

The point is, transforming from one to the other, hence to any similar vendor variation, is trivial.

p.s. It was only recently pointed out to me that functions in standard SQL are deliberately contrary, by having parameters lists that are not the conventional commalists, in order to make them easily identifiable as being from the standard!

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • 1
    Why does index start with 1 and not with 0? This returns the same result: SUBSTRING (my_column, 1, 1) = SUBSTRING (my_column, 0, 2). What is it in position 0? – FrenkyB Aug 26 '19 at 06:46
21

SUBSTRING ( MyColumn, 1 , 1 ) for the first character and SUBSTRING ( MyColumn, 1 , 2 ) for the first two.

Damovisa
  • 19,213
  • 14
  • 66
  • 88
17

It is simple to achieve by the following

DECLARE @SomeString NVARCHAR(20) = 'This is some string'
DECLARE @Result NVARCHAR(20)

Either

SET @Result = SUBSTRING(@SomeString, 2, 3)
SELECT @Result

@Result = his

or

SET @Result = LEFT(@SomeString, 6)
SELECT @Result

@Result = This i

jet_choong
  • 382
  • 3
  • 13
15
SELECT SUBSTR(thatColumn, 1, 1) As NewColumn from student
Devendra Verma
  • 938
  • 1
  • 13
  • 29
4

Select First two Character in selected Field with Left(string,Number of Char in int)

SELECT LEFT(FName, 2) AS FirstName FROM dbo.NameMaster
Samuel Philipp
  • 10,631
  • 12
  • 36
  • 56
Darshan
  • 51
  • 3
4

If you search the first char of string in Sql string

SELECT CHARINDEX('char', 'my char')

=> return 4
Emilio Gort
  • 3,475
  • 3
  • 29
  • 44
LittleJC
  • 133
  • 1
  • 5
3

INPUT

STRMIDDLENAME
--------------
Aravind Chaterjee
Shivakumar
Robin Van Parsee

SELECT STRMIDDLENAME, 
CASE WHEN INSTR(STRMIDDLENAME,' ',1,2) != 0 THEN SUBSTR(STRMIDDLENAME,1,1) || SUBSTR(STRMIDDLENAME,INSTR(STRMIDDLENAME,' ',1,1)+1,1)||
SUBSTR(STRMIDDLENAME,INSTR(STRMIDDLENAME,' ',1,2)+1,1)
WHEN INSTR(STRMIDDLENAME,' ',1,1) != 0 THEN SUBSTR(STRMIDDLENAME,1,1) || SUBSTR(STRMIDDLENAME,INSTR(STRMIDDLENAME,' ',1,1)+1,1)
ELSE SUBSTR(STRMIDDLENAME,1,1)
END AS FIRSTLETTERS
FROM Dual;

OUTPUT
STRMIDDLENAME                    FIRSTLETTERS
---------                        -----------------
Aravind Chaterjee                AC           
Shivakumar                       S
Robin Van Parsee                 RVP
Janak Nirmal
  • 22,706
  • 18
  • 63
  • 99
Shiv
  • 41
  • 1