How to Get First Character of a String in SQL

How to get first character of a string in SQL?

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.

Selecting first letter of each word in a string in SQL

Please try the following solution. It will work starting from SQL Server 2017 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col NVARCHAR(MAX));
INSERT INTO @tbl (col) VALUES
(N'John Doe'),
(N'Bob Dole'),
(N'Dan Roby'),
(N'Mary Smith Robert Polack');
-- DDL and sample data population, end

SELECT *,
     (SELECT STRING_AGG(LEFT(value,1), '')
            FROM STRING_SPLIT(col, SPACE(1))
            ) AS Result
FROM @tbl;

Output

+----+--------------------------+--------+
| ID |           col            | Result |
+----+--------------------------+--------+
|  1 | John Doe                 | JD     |
|  2 | Bob Dole                 | BD     |
|  3 | Dan Roby                 | DR     |
|  4 | Mary Smith Robert Polack | MSRP   |
+----+--------------------------+--------+

Get only first character with specific condition

You can use this:

SELECT LEFT(column_name, 1)
FROM table_name 
WHERE LTRIM(RTRIM(LEFT(column_name, 2))) LIKE '[A-Z]'

... or a solution using PATINDEX:

SELECT LEFT(column_name, 1) 
FROM table_name 
WHERE PATINDEX('[A-Z][^A-Z]%', column_name) > 0 OR PATINDEX('[A-Z]', column_name) > 0

... or a solution using LIKE:

SELECT LEFT(column_name, 1) 
FROM table_name 
WHERE column_name LIKE '[A-Z][^A-Z]%' OR column_name LIKE '[A-Z]'

demo on dbfiddle.uk

How to get first character-set of string in sql?

Use SUBSTRING_INDEX instead

SELECT SUBSTRING_INDEX('202500 135000', " ", 1) AS ExtractString;
SELECT SUBSTRING_INDEX('37500 25000', " ", 1) AS ExtractString;

| ExtractString |
| :------------ |
| 202500        |

| ExtractString |
| :------------ |
| 37500         |

db<>fiddle here

How do identify the first character of a string as numeric or character in SQL

Using LEFT and ISNUMERIC(), however be aware that ISNUMERIC thinks some additional characters such as . are numeric

UPDATE #decode
  SET SecondPartType =  
      CASE WHEN ISNUMERIC(LEFT(firstPartType, 1)) = 1 THEN'Numeric'
      ELSE 'Alpha'
      END 
FROM #decode;

Get first characters from a String

Edit:

In My SQL, you could use LEFT (or SUBSTRING) to SELECT certain number of characters from a string. Since what you need is the first characters, I suggest to use LEFT:

SELECT LEFT(field_value, 20) FROM MyTableName

Original:

(As the tag was originally C#, the following solution below provides solution for C# problem)

You should use Substring(0, 20). The first argument (0) is the starting index, and the second argument (20) is the length of the string you want to take.

For example:

var str = "123456789012345678901234567890";
str = str.Substring(0, 20);

Transact SQL - Get first character every 5 characters of a variable length string

Assuming you are using a fully supported version of SQL Server, and you do actually want to get the every 5th character, you could use a Tally and STRING_AGG to achieve this:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (8000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3, N N4)
SELECT STRING_AGG(SS.C,'') AS NewColumn
FROM (VALUES('A1234B1234C1234'))V(YourColumn)
     JOIN Tally T ON LEN(YourColumn) >= T.I
     CROSS APPLY (VALUES(SUBSTRING(V.YourColumn,T.I,1)))SS(C)
WHERE (T.I-1) % 5 = 0
GROUP BY V.YourColumn;

If, however, you actually just want to retain the alpha characters, I would use TRANSLATE and REPLACE:

SELECT REPLACE(TRANSLATE(V.YourColumn, '0123456789',REPLICATE('|',LEN('0123456789'))),'|','') AS NewColumn
FROM (VALUES('A1234B1234C1234'))V(YourColumn)

---

Note, if the value is a variable, I would recommend this method if using the Tally solution:

DECLARE @YourString varchar(8000) = 'A1234B1234C1234';

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (LEN(@YourString)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3, N N4)
SELECT STRING_AGG(SS.C,'') AS NewColumn
FROM Tally T
     CROSS APPLY (VALUES(SUBSTRING(@YourString,T.I,1)))SS(C)
WHERE (T.I-1) % 5 = 0;

First character of each word in a string

If your database data has NULL values in the column middle_name, then that function call will produce that error, because the function expects a String and NULL is not a string.

There are several ways to solve this, one of which is to adapt your query to convert NULL to an empty string before passing it to the function:

GetFirstLetters(Nz([middle_name], ""))

Or, you can keep your original function call, but then your function should accept a Variant type argument, and then deal with an Empty value:

Function GetFirstLetters(middlename As Variant) As String
    If IsEmpty(middlename) Then
        Exit Function
    End If
    ' Rest of your code ...
End Function

SQL select first letter of a word?

To get the first letter of a STRING you can use left:

SELECT LEFT(ColumnX, 1)

To do it for a word within a string is more complicated.

---

Related Topics