Split Housenumbers on chars and ints

Question

I have some fields in a db, with values of 54D, 325A, 2E and so on, letters first and numbers last.

How can I split those in a select statement, or filter it to only show the letters or numbers? I cant use udf functions for this.

I need to be able to insert 54 into another column and D into another and so on.

Im on MS SQL Server

Thanks

possible duplicate of [Extract numbers from a text in SQL Server](http://stackoverflow.com/questions/9629880/extract-numbers-from-a-text-in-sql-server) — Bridge, Mar 12 '15 at 13:32
Dont have the permissions for it - and it wont be givin, but still need to get the job done;) — user2199192, Mar 12 '15 at 13:35
and yes db means table - and fields means values/rows in a column;) — user2199192, Mar 12 '15 at 13:36
letter first and number last... you mean numbers first and letters last? please edit the question and correct the errors in the problem definition. — Tanner, Mar 12 '15 at 13:41

Alex K. · Answer 1 · 2015-03-12T14:04:11.930

4

One way using the position of the first non-digit:

;with T(f) as (
       select '325A' union
       select '54D' union
       select '2E' union
       select '555' union
       select 'Z'
)

select
    f,
    rtrim(left(f, patindex('%[^0-9]%', f + ' ') - 1)),
    rtrim(substring(f + ' ', patindex('%[^0-9]%', f + ' '), len(f)))
from T

---- 
2E      2     E 
325A    325   A 
54D     54    D 
555     555  
Z             Z

edited Mar 12 '15 at 14:04

answered Mar 12 '15 at 13:41

Alex K.

171,639
30
264
288

1

this is very nice, but it doesn't handle invalid data well. – t-clausen.dk Mar 12 '15 at 13:54

t-clausen.dk · Answer 2 · 2015-03-12T13:53:38.447

1

declare @t table(col1 varchar(20))
insert @t values('54D'),('325A'),('2E'), ('A'), ('3')

SELECT
  substring(col1, 0, patindex('%[^0-9]%', col1 + 'a')), 
  stuff('0' + col1, 1, patindex('%[^0-9]%', col1 + 'a'), '') 
FROM @t

Result:

edited Mar 12 '15 at 13:53

answered Mar 12 '15 at 13:48

t-clausen.dk

43,517
12
59
92

Thats perfect, just what I was looking for - could you explain what you did their in the select statement what it does? – user2199192 Mar 13 '15 at 07:12

Giorgi Nakeuri · Answer 3 · 2015-03-12T14:02:29.703

Here is some brutal solution, but it will be able to separate mixed strings also:

DECLARE @t TABLE(ID INT, S NVARCHAR(MAX))
INSERT INTO @t VALUES (1, '123AB'), (2, '45CDEF'), (3, '1AS^&*876YU')

DECLARE @m INT
SELECT @m = MAX(LEN(S)) FROM @t

;WITH cte AS
(SELECT ID, SUBSTRING(S, 1, 1) AS S, 1 AS N, ISNUMERIC(SUBSTRING(S, 1, 1)) AS NU FROM @t
 UNION ALL
 SELECT t.ID, SUBSTRING(t.S, N + 1, 1) AS S, N + 1 AS N, ISNUMERIC(SUBSTRING(t.S, N + 1, 1)) AS NU FROM cte
 JOIN @t t ON t.ID = cte.ID
 WHERE N < @m
)

SELECT 
(SELECT S FROM cte c2 WHERE c2.ID = c1.ID AND NU = 1 FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)') AS NumericPart,
(SELECT S FROM cte c2 WHERE c2.ID = c1.ID AND NU = 0 FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)') AS TextPart
FROM cte c1
WHERE S <> ''
GROUP BY ID

Output:

NumericPart TextPart
123         AB
45          CDEF
1876        AS^&*YU

Split Housenumbers on chars and ints

3 Answers3