Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

    Unanswered: Split a column's string-value into multiple columns, inserting in another table.

    Hello, I have a table with a string value, where all values are seperated by a space/blank. I now want to use SQL to split all the values and insert them into a different table, which then later will result in deleting the old table, as soon as I got all values out from it.

    Old Table:
    Code:
    ID, StringValue

    New Table:
    Code:
    ID, Value1, Value2
    Do note: Value1 is INT, Value2 is of nvarchar, hence Value2 can contain spaces... I just need to split on the FIRST space, then convert index[0] to int, and store index[1] as it is.
    Edit: I can split on all spaces and just Select them all and add them like so: SELECT t.val1 + ' ' + t.val2... If I cant find the first space that is... I mean, first 2-10 characters in the string can be integer, but does not have to be.
    Shall probably do it in code instead of SQL? Easier you say?

    Now I want to run a query that selects the StringValue from OldTable, splits the string by ' ' (a blank) and then inserts them into New Table.

    So far I've got this:

    Code:
    SELECT CASE CHARINDEX(' ', OldTable.stringvalue, 1)
    WHEN 0 THEN OldTable.stringvalue
    ELSE SUBSTRING(OldTable.stringvalue, 1, CHARINDEX(' ', OldTable.stringvalue, 1) - 1)
    END
    AS FirstWord
    FROM OldTable

    Edit: Found an example using strange things like CHARINDEX...

    But issue still remains, because the first word is of integer, or it does not have to be...If it isn't, there is not "first value", and the whole string shall be passed into "value2".
    How to detect if the very first character is of integer type?

    Code:
    @declare firstDigit int
    
    IF ISNUMERIC(SUBSTRING(@postal,2,1) AS int) = 1
       set @firstDigit = CAST(SUBSTRING(@postal,2,1) AS int)
    ELSE
       set @firstDigit = -1       
    
    RETURN @firstdigit
    ... I'll see if I can boild it together... somehow...

    Still feel free if you sit on a solution!
    Last edited by ManyTimes; 08-14-12 at 10:39.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    My first instinct is to try to rewrite the data model, if you have multiple pieces of data lumped into one field. This will get the first bit out in most cases. I have not taken into account things like special characters (e.g. $%^&*).
    Code:
    create table #temp
    (col1 varchar(200))
    
    insert into #temp values
    	('1234 this is a test'),
    	('02345 this is another test'),
    	('yet another test'),
    	('98765')
    -- end test data----------------
    with splits as
    (select case when charindex (' ', col1) = 0 then col1
    	else substring (col1, 1, charindex (' ', col1)) end as postCode,
    	case when charindex (' ', col1) = 0 then col1 
    	else substring (col1, charindex (' ', col1) + 1, 100) end as otherstuff
    from #temp)
    
    select case when postcode not like '%[A-Za-z]%' then postcode else null end,
    	case when postcode like '%[A-Za-z]%' then postcode + otherstuff else otherstuff end
    from splits

  3. #3
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

    So far, so good?

    Edit: Ok, thanks a lot MCrowley, that's almost what I am looking for, quite close, indeed...

    I should have been a bit more clear...

    I have an old table that is structured with postCode, Country, Street, Adress, and house number all in one line.
    I am now going to split that data up and insert them into new columns in a new table.


    The Table rows:
    "15125 Arizona WesterFall 13 USA"
    "Arizona Westerfall 13 USA"
    ""
    NULL

    Those 4 variations are the ones that exists in the database, no other. So if first character is integer, it is a postcal code, guruanteed. If it is not, there is no postal code.

    Do I need to split the "WITH CASE" query into two to get this to work? How can I get the result from the query above into my own table? Splits does not have column names....

    Ive tried:
    Code:
    DECLARE @temp2 as TABLE(postCode nvarchar(200), otherstuff nvarchar(200))
    
    	INSERT INTO @temp2(postCode, otherstuff)
    	VALUES(
    	with splits as
    	(select case when charindex (' ', col1) = 0 then col1
    		else substring (col1, 1, charindex (' ', col1)) end as postCode,
    		case when charindex (' ', col1) = 0 then col1 
    		else substring (col1, charindex (' ', col1) + 1, 100) end as otherstuff
    	from #temp)
    
    	select case when postcode not like '%[A-Za-z]%' then postcode else null end,
    		case when postcode like '%[A-Za-z]%' then postcode + otherstuff else otherstuff end
    	from splits
    	)
    ...

    Without luck.....



    Code:
    
    	DECLARE @temp2 as TABLE(postCode nvarchar(200), otherstuff nvarchar(200))
    	DECLARE @temp3 as TABLE(postCode nvarchar(200), otherstuff nvarchar(200))
    	INSERT INTO @temp2(postCode, otherstuff)
    	(
    		select case when charindex (' ', col1) = 0 then col1
    		else substring (col1, 1, charindex (' ', col1)) end as postCode,
    		case when charindex (' ', col1) = 0 then col1 
    		else substring (col1, charindex (' ', col1) + 1, 100) end as otherstuff
    		from #temp
    	)
    	INSERT INTO @temp3(postCode, otherstuff)
    	(
    		select case when postcode not like '%[A-Za-z]%' then postcode else null end,
    			case when postcode like '%[A-Za-z]%' then postcode + otherstuff else otherstuff end
    		from @temp2
    	)
    	SELECT * FROM @temp3
    Solved, but do not know why I needed two temp tables, to replace the WITH clause, does not matter, it is working. Thanks !
    Last edited by ManyTimes; 08-15-12 at 07:44.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •