substring

  • 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.

  • You can split the string by ' ' (single space) the you can take the second value for colour. similarly fo others

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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?

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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;

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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)

  • 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