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

1
votes

I have a string that looks like the following

A1234B1234C1234

I would like to take the first character every 5 characters.

The result would be

ABC

The string length is variable so the length could be 5, 10 , 20 , 30 ect

3
sql-servertsql
Where is this value stored? In a table? A variable? What version of SQL Server? – Larnu
you need only string value ignore number when you get first five right ? – Piyush Kachhadiya
I think the fact that the OP wants the letters could be a little misleading, @PiyushKachhadiya . Though, Stanton, it would be helpful to have more examples. Will the string always be in the format A0000A0000~? If so, then this could be much easier (again, pending SQL Server version). – Larnu

3 Answers

4
votes

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;
0
votes

try this

Create function UDF_ExtractAlphabets
(  
  @input varchar(255)  
)  
Returns varchar(255)  
As  
Begin  
  Declare @alphabetIndex int = Patindex('%[^a-zA-Z]%', @input)  
  Begin  
    While @alphabetIndex > 0  
    Begin  
      Set @input = Stuff(@input, @alphabetIndex, 1, '' )  
      Set @alphabetIndex = Patindex('%[^a-zA-Z]%', @input )  
    End  
  End  
  Return @input
End


SELECT left(dbo.UDF_ExtractAlphabets(yourtext),5)  FROM dbo.tablename

enter image description here

-1
votes

SELECT LEFT('yourtext', 5) AS firstFive FROM X;