SQL : Retrieve first three characters from sql column value

Question

For ex : if sql column value is sa,123k and the output should first three characters i.e. sak

Letters and any special characters needs to be eliminated and gets only three characters. How do we do this ?

If you are using SQL Server 2017, you can use `translate()`, otherwise this is rather difficult. Also, I think you mean "the first three *letters*". Everything in your question is a "character". — Gordon Linoff, Apr 12 '19 at 13:17
If you have access to a regex tool, then you would want to replace `[^A-Za-z]+` with empty string. If you have a major long term need for full regex support in your database, then perhaps look at something like Postgres or Oracle. — Tim Biegeleisen, Apr 12 '19 at 13:18
https://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function/23001916#23001916 — PM 77-1, Apr 12 '19 at 13:21

score 5 · Answer 1 · answered Apr 12 '19 at 13:23

You can use recursive CTEs for this purpose:

with t as (
      select 'sa,123k' as str
     ),
     cte as (
      select str, left(str, 1) as c, stuff(str, 1, 1, '') as rest, 1 as lev,
             convert(varchar(max), (case when left(str, 1) like '[a-zA-Z]' then left(str, 1) else '' end)) as chars
      from t
      union all
      select str, left(rest, 1) as c, stuff(rest, 1, 1, '') as rest, lev + 1,
             convert(varchar(max), (case when left(rest, 1) like '[a-zA-Z]' then chars + left(rest, 1) else chars end))
      from cte
      where rest > '' and len(chars) < 3
     )
select str, max(chars)
from cte
where len(chars) <= 3
group by str;

Here is a db<>fiddle.

Shakeer Mirza · Answer 2 · 2019-04-12T13:52:55.587

This might help

DECLARE @VAR VARCHAR(100)= 'sa,1235JSKL', @RESULT VARCHAR(MAX)=''

SELECT @RESULT = @RESULT+ 
CASE WHEN  RESULT LIKE '[a-zA-Z]' THEN RESULT ELSE '' END 
FROM (
SELECT NUMBER, SUBSTRING(@VAR,NUMBER,1) AS RESULT
FROM MASTER..spt_values 
WHERE TYPE = 'P' AND NUMBER BETWEEN 1 AND LEN(@VAR)
)A
ORDER BY NUMBER

SELECT SUBSTRING(@RESULT,1,3)

If you want to apply this on a Tables column, you need to create Scalar function with same logic. You can find more number of articles how to create the scalar function by Googling..

score 1 · Answer 3 · answered Apr 12 '19 at 14:24

You can use this function which is written by G Mastros to do this.

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp nvarchar(MAX))
Returns nvarchar(MAX)
AS
Begin

    Declare @KeepValues as nvarchar(MAX)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Then simply call the fuction like this

SELECT LEFT(dbo.RemoveNonAlphaCharacters(colName), 3)
FROM TableName

Reference: G Mastros answer on "How to strip all non-alphabetic characters from string in SQL Server" question.

SQL_M · Answer 4 · 2019-04-12T13:30:07.630

-1

Well, this is ugly, but you could replace all the characters you don't like.

In your example, this would be:

SELECT REPLACE (REPLACE (REPLACE (REPLACE ('sa,123k', '1', ''), '2', ''), '3', ''), ',', '')

Obviously, this needs a lot of replaces if you need all numbers and other sorts of characters replaced.

Edited, based on your comment:

SELECT REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE ('123456gh,.!879k', '1', ''), '2', ''), '3', ''), ',', ''), '4', ''), '5', ''), '6', ''), '.', ''), '!', ''), '7', ''), '8', ''), '9', '')

edited Apr 12 '19 at 13:30

answered Apr 12 '19 at 13:21

SQL_M

2,455
2
16
30

IF column value is 123456gh,.!879k then it will be difficult to find out using your script. I have lots of values like this to find out , I think best option is looping through but I am not sure how to do – Sak SK Apr 12 '19 at 13:24

SQL : Retrieve first three characters from sql column value

4 Answers4