Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# tokenizing strings in sql

Posted on 2006-06-15
Medium Priority
444 Views
Hi,

I have data in an 'Address' column which needs to be migrated to new table whose

could anyone help me write an sql query that can cut up a string into four component strings.
The cut token would be a new line character with any whitespace character(including other newline) to the left or right.

ie

"Jon smith
Melbourne University
Australia"

would be cut up into

ie
"Henry Smith

Economics Faculty

Melbourne University
Victoria
Australia.
Earth."

would be cup into
"Victoria
Australia.
Earth"

Any help on this would be much appreciated
0
Question by:santuon
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 2

Accepted Solution

ewahner earned 2000 total points
ID: 16910449
First you want to replace the data with something a little easier to parse like this and then run it through this function:

CREATE FUNCTION dbo.fnArray ( @Str varchar(1000), @Delim varchar(1) = ' ' )
returns  @tmpTable table ( arrValue varchar(25))
as
begin
declare @pos integer
declare @lastpos integer
declare @arrdata varchar(1000)
declare @data varchar(25)

set @arrdata = replace(replace(replace(replace(upper(@Str),@Delim,'|'),'-','|'),'/','|'),'\','|')
set @arrdata = @arrdata + '|'
set @lastpos = 1
set @pos = 0
set @pos = charindex('|', @arrdata)
while @pos <= len(@arrdata) and @pos <> 0
begin
set @data = substring(@arrdata, @lastpos, (@pos - @lastpos))
if rtrim(ltrim(@data)) > ''
begin
if not exists( select top 1 arrValue from @tmpTable where arrValue = @data )
begin
insert into @tmpTable ( arrValue ) values ( @data )
end
end
set @lastpos = @pos + 1
set @pos = charindex('|', @arrdata, @lastpos)
end
return
end

declare @str varchar(255)
set @str = 'Henry Smith
Economics Faculty
Melbourne University
Victoria
Australia.
Earth'

set @str = replace(replace(@str,char(13),'|'),char(10),'')
select * from dbo.fnArray(@str,'|')
0

LVL 6

Expert Comment

ID: 16910622
0

LVL 2

Expert Comment

ID: 17030199
Mine is more efficient as it uses the charindex to move to the next delimiter, whereas the devx one loops thru each character...very inefficient.  Also you can take out the if and just do the insert:

--        if not exists( select top 1 arrValue from @tmpTable where arrValue = @data )
--        begin
insert into @tmpTable ( arrValue ) values ( @data )
--        end

If you don't care about non-unique values.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
###### Suggested Courses
Course of the Month6 days, 20 hours left to enroll