Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

substring Expand / Collapse
Author
Message
Posted Tuesday, August 17, 2010 10:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 12,881, Visits: 31,813
ny66 (8/17/2010)
if i use replace, i still cant get the values in the columns i need due to length of the field. there is no set length on the first value it hits.


spaces, tabs and CrLf can make all the difference in the world here....

can you post the example data as an actual INSERT INTO query?(don't paste my example back...that already worked correctly)

what i created could be radically different than the actual data; since the devil is in the details here, without your data for us to test against, we can't help out.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #970600
Posted Friday, August 20, 2010 10:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 23, 2010 10:31 AM
Points: 17, Visits: 32
this is what i am using currently, but all the values are not being populated properly.

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
CREATE TABLE #Temp (ID INT Identity, OrigField VarChar(Max),f1 VarChar(100),
f2 VarChar(100),f3 VarChar(100),f4 VarChar(100),f5 VarChar(100),f6 VarChar(100),
f7 VarChar(100),f8 VarChar(100),f9 VarChar(100),f10 VarChar(100))

INSERT INTO #Temp (OrigField)
SELECT
(ns.f1) AS OrigField
FROM dbo.NEWDUNDD_SEAF0 AS ns

DECLARE @ID INT, @f VarChar(100), @Remainder VarChar(Max)

WHILE EXISTS (SELECT TOP 1 * FROM #Temp WHERE f1 IS NULL)
BEGIN
SET @ID = (SELECT TOP 1 ID FROM #Temp WHERE f1 IS NULL)
SET @Remainder = (SELECT ((OrigField)) + ' ' FROM #Temp WHERE ID = @ID)
WHILE LEN(@Remainder) > 0
BEGIN
SET @f = LEFT(@Remainder, PATINDEX('% %', @Remainder) -1)
IF (SELECT ID FROM #Temp WHERE ID = @ID AND f1 IS NULL) = @ID UPDATE #Temp SET f1 = @f WHERE ID = @ID
ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f2 IS NULL) = @ID UPDATE #Temp SET f2 = @f WHERE ID = @ID
ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f3 IS NULL) = @ID UPDATE #Temp SET f3 = @f WHERE ID = @ID
ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f4 IS NULL) = @ID UPDATE #Temp SET f4 = @f WHERE ID = @ID
ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f5 IS NULL) = @ID UPDATE #Temp SET f5 = @f WHERE ID = @ID
ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f6 IS NULL) = @ID UPDATE #Temp SET f6 = @f WHERE ID = @ID
ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f7 IS NULL) = @ID UPDATE #Temp SET f7 = @f WHERE ID = @ID
ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f8 IS NULL) = @ID UPDATE #Temp SET f8 = @f WHERE ID = @ID
ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f9 IS NULL) = @ID UPDATE #Temp SET f9 = @f WHERE ID = @ID
ELSE IF (SELECT ID FROM #Temp WHERE ID = @ID AND f10 IS NULL) = @ID UPDATE #Temp SET f10 = @f WHERE ID = @ID
SET @Remainder = RIGHT(@Remainder, LEN(@Remainder) - PATINDEX('% %', @Remainder)+1)
END
END

SELECT * FROM #Temp;
Post #972673
Posted Friday, August 20, 2010 11:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 12,881, Visits: 31,813
Lowell (8/17/2010)

can you post the example data as an actual INSERT INTO query?(don't paste my example back...that already worked correctly)


chopping stuff up with substrings not hard to do...but we need the sample data to help.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #972701
Posted Friday, August 20, 2010 11:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 23, 2010 10:31 AM
Points: 17, Visits: 32
this is a data example of what i am attempting to parse into columns. i have tried the substring, not working so well not standard positions on the values

(blank spaces)24W 529 22 132 110 123 -13 516 (one row of data)

3102 DENIM 8 99 0 213 213 0 213 312 (another row data)

66552266 green blue 77p 1111 2222 33333 (another row)
Post #972723
Posted Friday, August 20, 2010 11:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 23, 2010 10:31 AM
Points: 17, Visits: 32
havent gotten that far yet on the insert into, need to get the syntax together prior to creating the new table.
Post #972725
Posted Friday, August 20, 2010 12:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 12,881, Visits: 31,813
ny66 (8/20/2010)
havent gotten that far yet on the insert into, need to get the syntax together prior to creating the new table.


it's pretty basic:
--get just the data we need to test against
SELECT
(ns.f1) AS f1
INTO #temp
FROM dbo.NEWDUNDD_SEAF0
--pazste the results to SQL Server Central:
SELECT 'INSERT INTO #TEMP(f1) VALUES('' + f1 + '') UNION ALL '
from #temp



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #972736
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse