Tutorial :How to get first character of a string in SQL?



Question:

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?


Solution:1

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.


Solution:2

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!


Solution:3

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


Solution:4

It is simple to achieve this by following:

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

and either

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

@Result: his

-OR-

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

@Result: This i


Solution:5

SELECT SUBSTR(thatColumn, 1, 1) As NewColumn from student  


Solution:6

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  


Solution:7

If you search the first char of string in Sql string

SELECT CHARINDEX('char', 'my char')    => return 4  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »