none
split the field

    Question

  • Hi,

    I have a field with URL's as values like: 

    Column1
    http://social.msdn.microsoft.com/Forums/en/category/sqlserver
    http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/threads
    http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/threads

    Now I want to break this into 2 fields like:

    Column1                                                                          Column2
    http://social.msdn.microsoft.com/Forums                       en/category/sqlserver
    http://social.msdn.microsoft.com/Forums                       en/sqlintegrationservices/threads
    http://social.msdn.microsoft.com/Forums                       en/sqlintegrationservices/threads

    I have written a query using the Substring and Charindex, but unable to get the expected result. Can anyone let me know the correct syntax to get this results.

    Thanks

    Tuesday, February 26, 2013 12:04 AM

Answers

  • create table test (column1 varchar(4000))
    insert into test(column1) values  ('http://social.msdn.microsoft.com/net'),
    ('http://social.msdn.microsoft.com//net/en/category/sqlserver'),
    ('http://social.msdn.microsoft.com/net/')
    Select Column1,NULL from Test where Len(Column1)-LEN(Replace(Column1,'/',''))<4
    Union 
    SELECT
           REPLACE(substring(Column1,1,dbo.[NThcharindex]('/',column1,1,4)-1),'"','') Column2,
            REPLACE(SUBSTRING(column1,dbo.[NThcharindex]('/',column1,1,4)+1,len(column1)),'"','') [column3]
    from TEST where LEN(Column1) - LEN(REPLACE(Column1, '/', '')) >=4
    
    drop table TEST


    Hope it Helps!!


    • Edited by Stan210 Tuesday, February 26, 2013 4:34 AM
    • Marked as answer by K.Kalyan Tuesday, February 26, 2013 4:41 AM
    Tuesday, February 26, 2013 4:33 AM

All replies

  • declare @t varchar(max)
    set @t = 'http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/threads'
    --set @t = 'abababab'
    select substring(@t,1,dbo.[NThcharindex]('/',@t,1,4)-1),SUBSTRING(@t,dbo.[NThcharindex]('/',@t,1,4)+1,len(@T))

    Create the function refered here first and run the code above.. http://jobjohncherubs.blogspot.com/2012/11/nth-charindex.html

    Assumption , you want to split the string at the fourth occurrence of '/'...


    Hope it Helps!!

    • Marked as answer by K.Kalyan Tuesday, February 26, 2013 1:53 AM
    • Unmarked as answer by K.Kalyan Tuesday, February 26, 2013 3:33 AM
    Tuesday, February 26, 2013 12:41 AM
  • Try this,
    declare @t varchar(max)
    set @t = 'http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/threads'
    
    select SUBSTRING(@t,40,DATALENGTH(@t))
    
    select SUBSTRING(@t,charindex('/en',@t),DATALENGTH(@t))
    
    select RIGHT(@t,charindex('/sm',reverse(@t)))
    
    
    



    Regards, Dineshkumar
    Please "Mark as Answer" if my post answers your question or "Vote as Helpful" if it helps you

    Tuesday, February 26, 2013 1:58 AM
  • create table test (column1 varchar(4000))
    insert into test values ('http://social.msdn.microsoft.com/Forums/en/category/sqlserver'),
    ('http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/threads'),
    ('http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/threads')
    
    ;with mycte1 as
    (select LEFt(column1,patindex('%[a-z]/[a-z]%',column1)) str1, substring(column1,patindex('%[a-z]/[a-z]%',column1)+2,len(column1)) str2 from test)
    
    SELECT str1+'/'+Left(str2, patindex('%[a-z]/[a-z]%',str2)) as col1,
    substring(str2,patindex('%[a-z]/[a-z]%',str2)+2,len(str2)) col2 
     from mycte1
    
    
    drop table test

    Tuesday, February 26, 2013 1:58 AM
  • Hi Stan,

    If I use this function, I'm getting the error as

    Invalid length parameter passed to the LEFT or SUBSTRING function.
    Tuesday, February 26, 2013 3:35 AM
  • what is your query??? Try this query ..

    create table test (column1 varchar(4000)) insert into test values ('http://social.msdn.microsoft.com/Forums/en/category/sqlserver'), ('http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/threads'), ('http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/threads') --for better understanding, I broke it down.. select substring(Column1,1,dbo.[NThcharindex]('/',Column1,1,4)-1) from TEST

    Select SUBSTRING(Column1,dbo.[NThcharindex]('/',Column1,1,4)+1,len(Column1)) from TEST

    Let us know, what is your exact problem??


    Hope it Helps!!



    • Edited by Stan210 Tuesday, February 26, 2013 4:00 AM
    Tuesday, February 26, 2013 3:52 AM
  • SELECT
           REPLACE(substring(Column1,1,dbo.[NThcharindex]('/',column1,1,4)-1),'"','') Column2,
            REPLACE(SUBSTRING(column1,dbo.[NThcharindex]('/',column1,1,4)+1,len(column1)),'"','') [column3]

    I do have some values like: 
    Column1
    http://social.msdn.microsoft.com/net
    http://social.msdn.microsoft.com//net/en/category/sqlserver'
    http://social.msdn.microsoft.com/net/

    With the above query,If I do top 100, I'm getting the results for some of the data as:
    Col2 col3
    http://social.msdn.microsoft.com/net http://social.msdn.microsoft.com/net (This is not expected)
    http://social.msdn.microsoft.com/ net/en/category/sqlserver
    http://social.msdn.microsoft.com/net/ <blank>

    • Edited by K.Kalyan Tuesday, February 26, 2013 4:13 AM edit
    Tuesday, February 26, 2013 3:59 AM
  • I ran your query and it is working fine...
    create table test (column1 varchar(4000))
    insert into test(column1) values  ('http://social.msdn.microsoft.com/Forums/en/category/sqlserver'),
    ('http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/threads'),
    ('http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/threads')
    
    
    SELECT
           REPLACE(substring(Column1,1,dbo.[NThcharindex]('/',column1,1,4)-1),'"','') Column2,
            REPLACE(SUBSTRING(column1,dbo.[NThcharindex]('/',column1,1,4)+1,len(column1)),'"','') [column3]
    from TEST
    drop table TEST

     Looks like some issue with your input?? could you  please check that...



    • Edited by Stan210 Tuesday, February 26, 2013 4:10 AM
    Tuesday, February 26, 2013 4:08 AM
  • SELECT
           REPLACE(substring(Column1,1,dbo.[NThcharindex]('/',column1,1,4)-1),'"','') Column2,
            REPLACE(SUBSTRING(column1,dbo.[NThcharindex]('/',column1,1,4)+1,len(column1)),'"','') [column3]

    I do have some values like: 
    Column1
    http://social.msdn.microsoft.com/net
    http://social.msdn.microsoft.com//net/en/category/sqlserver'
    http://social.msdn.microsoft.com/net/

    With the above query,If I do top 100, I'm getting the results for some of the data as:
    Col2 col3
    http://social.msdn.microsoft.com/net http://social.msdn.microsoft.com/net (This is not expected)
    http://social.msdn.microsoft.com/ net/en/category/sqlserver
    http://social.msdn.microsoft.com/net/ <blank>

    Could you please tell us sample output for these three inputs..

    http://social.msdn.microsoft.com/net

    http://social.msdn.microsoft.com//net/en/category/sqlserver'

    http://social.msdn.microsoft.com/net/


    Hope it Helps!!

    Tuesday, February 26, 2013 4:20 AM
  • try this

    SELECT
           REPLACE(substring(Column1,1,dbo.[NThcharindex]('/',column1,1,4)-1),'"','') Column2,
            REPLACE(SUBSTRING(column1,dbo.[NThcharindex]('/',column1,1,4)+1,len(column1)),'"','') [column3]
    from TEST where LEN(Column1) - LEN(REPLACE(Column1, '/', '')) >=4

    The reason why first one fails is  because the first input does not have 4 '/' 's in it..that's why it errors out, In the query above I added condition to see if '/' exists at least 4 times. This will eliminate the column1 with less than 4 '/'..if want those as well, you can do

    Select Column1,NULL from Test where Len(Column1)-LEN(Replace(Column1,'/',''))<4
    Union 
    SELECT
           REPLACE(substring(Column1,1,dbo.[NThcharindex]('/',column1,1,4)-1),'"','') Column2,
            REPLACE(SUBSTRING(column1,dbo.[NThcharindex]('/',column1,1,4)+1,len(column1)),'"','') [column3]
    from TEST where LEN(Column1) - LEN(REPLACE(Column1, '/', '')) >=4


    Hope it Helps!!


    • Edited by Stan210 Tuesday, February 26, 2013 4:30 AM
    Tuesday, February 26, 2013 4:25 AM
  •  Sample OUT:

    Column2                                                                                   column3
    http://social.msdn.microsoft.com/net                                    http://social.msdn.microsoft.com/net (This is not expected)
    http://social.msdn.microsoft.com/                                         net/en/category/sqlserver
    http://social.msdn.microsoft.com/net/                                   <Blank>

    And yes, I do have some bad values in the input as:

    Columns1
              _export"
                PHOTS
                TEXTO EN CON

    Tuesday, February 26, 2013 4:25 AM
  • create table test (column1 varchar(4000))
    insert into test(column1) values  ('http://social.msdn.microsoft.com/net'),
    ('http://social.msdn.microsoft.com//net/en/category/sqlserver'),
    ('http://social.msdn.microsoft.com/net/')
    Select Column1,NULL from Test where Len(Column1)-LEN(Replace(Column1,'/',''))<4
    Union 
    SELECT
           REPLACE(substring(Column1,1,dbo.[NThcharindex]('/',column1,1,4)-1),'"','') Column2,
            REPLACE(SUBSTRING(column1,dbo.[NThcharindex]('/',column1,1,4)+1,len(column1)),'"','') [column3]
    from TEST where LEN(Column1) - LEN(REPLACE(Column1, '/', '')) >=4
    
    drop table TEST


    Hope it Helps!!


    • Edited by Stan210 Tuesday, February 26, 2013 4:34 AM
    • Marked as answer by K.Kalyan Tuesday, February 26, 2013 4:41 AM
    Tuesday, February 26, 2013 4:33 AM
  • Hi Stan,

    I have changes my query to 

    SELECT
           REPLACE(substring(Column1,1,dbo.[NThcharindex]('/',column1+'/',1,4)-1),'"','') Column2,
            REPLACE(SUBSTRING(column1,dbo.[NThcharindex]('/',column1+'/',1,4)+1,len(column1)),'"','') [column3]
    from TEST where LEN(Column1) - LEN(REPLACE(Column1, '/', '')) >=3

    Tuesday, February 26, 2013 4:33 AM