Add Space between text and number in same field

Question

Post reply

Add Space between text and number in same field

Melissa Manning

SSC Veteran

Points: 259
More actions
July 23, 2010 at 2:21 pm

#97487

I have data in a column of our database...
CHEM0109
MGMT532
MECH5100
TRAN100
TRAN100
BIO1323
ENG1213
TRAN100
SPDR
TRAN100
TRAN100
I need to add a space between the text and number. Sometimes there is not a text/number in the field and there isn't a fixed text/number length.
Does anyone know of a way to add this space? Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

I'm pretty sure there is a more elegant way to code this, but this will do what you're asking:

declare @t_tally table (N int) --hopefully you have a real tally table to work with

insert into @t_tally

select 1 union select 2 union select 3 union select 4 union select 5 union

select 6 union select 7 union select 8 union select 9 union select 10

declare @t_data table (column1 varchar(10))

insert @t_data

SELECT 'CHEM0109' UNION ALL

SELECT 'MGMT532' UNION ALL

SELECT 'MECH5100' UNION ALL

SELECT 'TRAN100' UNION ALL

SELECT 'BIO1323' UNION ALL

SELECT 'ENG1213' UNION ALL

SELECT 'TRAN100' UNION ALL

SELECT 'SPDR' UNION ALL

SELECT 'TRAN100' UNION ALL

SELECT 'TRAN100'

SELECT td.column1,

newColumn = CASE ISNULL(sq.pos,0)

WHEN 0 THEN td.column1

ELSE STUFF(td.column1,sq.pos,0,' ') END

FROM

@t_data td

LEFT JOIN

(SELECT d.column1, MIN(t.N) as pos

FROM @t_data d

JOIN @t_tally t ON t.N <= LEN(d.column1)

WHERE ISNUMERIC(SUBSTRING(d.column1,N,1)) = 1

GROUP BY d.column1) sq

ON sq.column1 = td.column1

If you could possible have data starting with a numeric character this would need to be modified.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Louisa Whitton SSCrazy Points: 2577 More actions · Answer 2

I don't know if you consider it more elegant or not, but you can achieve the same result using patindex, eg:

declare @data table (DataValue varchar(10))

insert into @data

select 'CHEM0109' union all

select 'MGMT532' union all

select 'MECH5100' union all

select 'TRAN100' union all

select 'BIO1323' union all

select 'ENG1213' union all

select 'TRAN100' union all

select 'SPDR' union all

select 'TRAN100' union all

select 'TRAN100'

select

case

when DataValue like '[A-Z]%[0-9]'

then left(DataValue,patindex('%[A-Z][0-9]%',DataValue))+' '+convert(varchar,right(DataValue,len(DataValue)-patindex('%[A-Z][0-9]%',DataValue)))

else DataValue

end

from

@data

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

DROP table #data

CREATE table #data (DataValue varchar(10))

insert into #data

select 'CHEM0109' union all

select 'MGMT532' union all

select 'MECH5100' union all

select 'TRAN100' union all

select 'BIO1323' union all

select 'ENG1213' union all

select 'TRAN100' union all

select 'SPDR' union all

select 'TRAN100' union all

select '1001'

SELECT LTRIM(STUFF(DataValue, PATINDEX('%[A-Z][0-9]%',DataValue)+1, 0, ' '))

FROM #data

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 4

However, This is not a good approach, but I could find only this solution by using T-SQL....

select c1, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(c1, '0', ''), '1', ''), '2', '') , '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')

, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(c1, '0', ''), '1', ''), '2', '') , '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')

+ space(1) + replace(c1, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(c1, '0', ''), '1', ''), '2', '') , '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', ''), '')

from asd2

--replace c1 with col_Name and asd2 with table_Name

Jerry Day SSC-Addicted Points: 454 More actions · Answer 5

-- Create a tally table as taught by Jeff Moden

SELECT top 30 IDENTITY(int) ii into aux from sys.objects;

--------------------------------------------------------

SELECT col1, STUFF(col1, MIN(ii), 0, ' ')

FROM

(

SELECT col1, ii

FROM Table1

CROSS JOIN aux

WHERE SUBSTRING(col1, ii, 1) >= CHAR(48) AND SUBSTRING(col1, ii, 1) <= CHAR(57)

) XX

GROUP BY col1

union all

SELECT col1, col1

FROM Table1

WHERE col1 LIKE '%[^0-9]'

Jerry D

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 6

Here's another solution using PATINDEX and STUFF.

EDIT: Just noticed my solution was identical to Chris Morris' so have removed it.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

andrewd.smith (10/27/2010)
Here's another solution using PATINDEX and STUFF.
EDIT: Just noticed my solution was identical to Chris Morris' so have removed it.

Andrew I'm gonna 'fess up mate my solution IS yours, I knicked it tomorrow using DBCC TIMEWARP.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden