CODEWITHSUNDEEP
sql-servert-sqlstring-parsing
JoBaxter
JoBaxter

Reputation: 721

How to split a non delimited string in T-SQL

I need to split a string value that has no delimiter. I work in banking and I am selecting a GL account number and need to separate the account number from the account branch number. The issue is both values are passed as one long string, 10 digits for the account number and 4 for the account branch. For example 01234567891234 needs to be changed to 0123456789.1234.

Every thing I find says to use CHARINDEX or SUBSTRING. From my understand both require a character to search for. If anyone can provide another function and some example code that would be great. Thanks.

Upvotes: 1

Views: 937

Answers (2)

Kaf
Kaf

Reputation: 33809

You could also use STUFF function as below:

declare @accNo varchar(14) = '01234567891234'

select stuff(@accNo,11,0,'.')

SQL Fiddle

Upvotes: 2

Derek
Derek

Reputation: 23228

You can do something simple like

left(str, 10) + '.' + right(str, 4)

if you know it'll always be a 14 character string

Upvotes: 2

Related Questions