August 9, 2010 at 7:50 am
i am using the following to parse data from a string, but it only works properly if the values are in the exact place in the string.
SELECT SUBSTRING( f1, 1, 4 ) as style,
SUBSTRING( f1, 17, 9 ) as color,
SUBSTRING( f1, 34, 3 ) as size_1,
SUBSTRING( f1, 37, 3 ) as size_2
from dbo.NEWDUNDD_SEACX
the following is what my string looks like where i am trying to retrieve the values.
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)
you see that they are never exactly in the same place. I would like to know if there is a way to retrieve the records based upon a space between the values.
August 9, 2010 at 8:03 am
You can split the string by ' ' (single space) the you can take the second value for colour. similarly fo others
August 9, 2010 at 9:09 am
here's an example using a cvustom function CHARINDEX2, which finds the nth occurrance of a string...in your case a space...
/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
CREATE FUNCTION CHARINDEX2(
@TargetStr varchar(8000),
@SearchedStr varchar(8000),
@Occurrence int)
RETURNS int
AS
BEGIN
DECLARE @pos int, @counter int, @ret int
SET @pos = CHARINDEX(@TargetStr, @SearchedStr)
SET @counter = 1
IF @Occurrence = 1
SET @ret = @pos
ELSE
BEGIN
WHILE (@counter < @Occurrence)
BEGIN
SELECT @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)
SET @counter = @counter + 1
SET @pos = @ret
END
END
RETURN(@ret)
END
GO
/*
---------------------------------------- ---------------------------------------- ----------------------------------------
24W 529 22
3102 DENIM 8
66552266 green blue
*/
SELECT
SUBSTRING(TheExample,1,dbo.CHARINDEX2(' ', TheExample, 1)),
SUBSTRING(TheExample,dbo.CHARINDEX2(' ', TheExample, 1),dbo.CHARINDEX2(' ', TheExample, 2) - dbo.CHARINDEX2(' ', TheExample, 1)),
SUBSTRING(TheExample,dbo.CHARINDEX2(' ', TheExample, 2),dbo.CHARINDEX2(' ', TheExample, 3) - dbo.CHARINDEX2(' ', TheExample, 2))
from (
SELECT '24W 529 22 132 110 123 -13 516 ' AS TheExample UNION ALL
SELECT '3102 DENIM 8 99 0 213 213 0 213 312 ' UNION ALL
SELECT '66552266 green blue 77p 1111 2222 33333 '
) X
Lowell
August 9, 2010 at 9:29 am
thank you very much, that is exactly what i am looking for to retrieve the data to columns,
how do i create a table from that, can i just replace create function with create table?
do i need to build a table from the function?
August 11, 2010 at 8:10 am
Some additional assistance please. I used this to create a function, but how do i get it to a table? i have done some searching but im stumped. it creates a function.
any guidance will be appreciated. thanks
CREATE FUNCTION DBO.test(
@TargetStr varchar(8000),
@SearchedStr varchar(8000),
@Occurrence int)
RETURNS int
AS
BEGIN
DECLARE @pos int, @counter int, @ret int
SET @pos = CHARINDEX(@TargetStr, @SearchedStr)
SET @counter = 1
IF @Occurrence = 1
SET @ret = @pos
ELSE
BEGIN
WHILE (@counter < @Occurrence)
BEGIN
SELECT @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)
SET @counter = @counter + 1
SET @pos = @ret
END
END
RETURN(@ret)
END
GO
/*
---------------------------------------- ---------------------------------------- ----------------------------------------
24W 529 22
3102 DENIM 8
66552266 green blue
*/
SELECT
SUBSTRING(TheExample,1,dbo.CHARINDEX2(' ', TheExample, 1)),
SUBSTRING(TheExample,dbo.CHARINDEX2(' ', TheExample, 1),dbo.CHARINDEX2(' ', TheExample, 2) - dbo.CHARINDEX2(' ', TheExample, 1)),
SUBSTRING(TheExample,dbo.CHARINDEX2(' ', TheExample, 2),dbo.CHARINDEX2(' ', TheExample, 3) - dbo.CHARINDEX2(' ', TheExample, 2))
from (
SELECT 'F1' AS TheExample
FROM dbo.NEWDUNDD_SEACX/*UNION ALL
SELECT '3102 DENIM 8 99 0 213 213 0 213 312 ' UNION ALL
SELECT '66552266 green blue 77p 1111 2222 33333 '*/
) X
August 11, 2010 at 8:24 am
originally you had posted that you had a table dbo.NEWDUNDD_SEACX with a column f1
because you had no CREATE TABLE or INSERT INTO scripts, i created my own table wth the column...TheExample
i think you are looking for something like this? you could create a view with code similar to this.
SELECT
style = SUBSTRING(f1,1,dbo.CHARINDEX2(' ', f1, 1)),
color = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 1),dbo.CHARINDEX2(' ', f1, 2) - dbo.CHARINDEX2(' ', f1, 1)),
size_1 = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 2),dbo.CHARINDEX2(' ', f1, 3) - dbo.CHARINDEX2(' ', f1, 2)),
size_2 = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 3),dbo.CHARINDEX2(' ', f1, 4) - dbo.CHARINDEX2(' ', f1, 3))
from dbo.NEWDUNDD_SEACX
Lowell
August 11, 2010 at 8:46 am
Thanks, that is basically what i am looking for, i apologize for being slow on getting this, but i want to understand as well as, get it working.
the first substring picks up the first values, but then the other columns are blank,
SELECT
style = SUBSTRING(f1,1,dbo.CHARINDEX2(' ', f1, 1)),-- this picks up the first values in the string.
the following are all blank.
color = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 1),dbo.CHARINDEX2(' ', f1, 2) - dbo.CHARINDEX2(' ', f1, 1)),
size_1 = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 2),dbo.CHARINDEX2(' ', f1, 3) - dbo.CHARINDEX2(' ', f1, 2)),
size_2 = SUBSTRING(f1,dbo.CHARINDEX2(' ', f1, 3),dbo.CHARINDEX2(' ', f1, 4) - dbo.CHARINDEX2(' ', f1, 3))
from dbo.NEWDUNDD_SEACX
if i understand properly, in the substring it is looking at the blank space, column, position ('', f1, 1) so it should find the blank space after the position, then search the column again starting after the blank space at postition 2???
hope this makes sense.
August 11, 2010 at 10:03 am
yes that's correct...but it depends on your data as to whether it will be blank or find a value...i assumeed one space between each ite,.
if there are TWO spaces between each element in your data, it would probably find every other item..3 spaces between each, then it would find nothing but the first item in our example.
you'll need to look at the actual data in f1: what does the column actually contain?
Lowell
August 12, 2010 at 7:31 am
you should try whit the function replace, maybe its the solution for your problem.
I can see that your´s strings have a variable lenght, you should try to delimite each substring like you need.
select REPLACE('24W 529 22 132 110 123 -13 516',' ','')
select REPLACE('3102 DENIM 8 99 0 213 213 0 213 312',' ','')
select REPLACE('66552266 green blue 77p 1111 2222 33333',' ','')
See you
August 17, 2010 at 10:34 am
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.
August 17, 2010 at 10:40 am
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
August 20, 2010 at 10:21 am
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;
August 20, 2010 at 11:04 am
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
August 20, 2010 at 11:42 am
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)
August 20, 2010 at 11:42 am
havent gotten that far yet on the insert into, need to get the syntax together prior to creating the new table.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply