Post reply

Extarct Words With Specific Format in SQL Column(text)

  • January 31, 2014 at 5:01 am

    #285072

    hi

    I have Table like this

    clndr_idclndr_data

    178(0||CalendarData()((0||DaysOfWeek()((0||1()())(0||2()((0||0(f|16:00|s|08:00)())))(0||3()((0||0(f|16:00|s|08:00)())))(0||4()((0||0(f|16:00|s|08:00)())))(0||5()((0||0(f|16:00|s|08:00)())))(0||6()((0||0(f|16:00|s|08:00)())))(0||7()())))(0||Exceptions()((0||0(d|41519)())(0||1(d|41155)())(0||2(d|40364)())(0||3(d|40910)())(0||4(d|41633)())(0||5(d|41094)())(0||6(d|40427)())(0||7(d|40536)())(0||8(d|41235)())(0||9(d|40179)())(0||10(d|40903)())(0||11(d|40543)())(0||12(d|40791)())(0||13(d|41421)())(0||14(d|40728)())(0||15(d|41275)())(0||16(d|41606)())(0||17(d|40329)())(0||18(d|41459)())(0||19(d|40871)())(0||20(d|40507)())(0||21(d|41057)())(0||22(d|40693)())(0||23(d|41268)())))))

    179(0||CalendarData()( (0||DaysOfWeek()( (0||1()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||2()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||3()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||4()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||5()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||6()()) (0||7()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))))) (0||VIEW(ShowTotal|N)()) (0||Exceptions()( (0||0(d|36525)()) (0||1(d|36675)()) (0||2(d|36711)()) (0||3(d|36773)()) (0||4(d|36853)()) (0||5(d|36854)()) (0||6(d|36885)()) (0||7(d|36892)()) (0||8(d|37039)()) (0||9(d|37076)()) (0||10(d|37137)()) (0||11(d|37217)()) (0||12(d|37218)()) (0||13(d|37250)()) (0||14(d|37257)()) (0||15(d|37441)()) (0||16(d|37615)()) (0||17(d|37622)()) (0||18(d|37806)()) (0||19(d|37980)()) (0||20(d|37987)()) (0||21(d|38173)()) (0||22(d|38345)()) (0||23(d|38352)()) (0||24(d|38537)()) (0||25(d|38712)()) (0||26(d|38719)()) (0||27(d|38902)()) (0||28(d|39076)()) (0||29(d|39083)()) (0||30(d|39267)()) (0||31(d|39441)()) (0||32(d|39448)()) (0||33(d|39633)()) (0||34(d|39807)()) (0||35(d|39814)()) (0||36(d|39997)()) (0||37(d|40172)()) (0||38(d|40179)()) (0||39(d|40364)()) (0||40(d|40536)()) (0||41(d|41164)()) (0||42(d|41216)()) (0||43(d|41237)()) (0||44(d|41238)())))))

    but I want to have like this

    17841519

    17841155

    17840364

    17840910

    17841633

    ….….

    17936525

    17936675

    17936711

    ….…..

    Can anyone help me?

    thanks

  • January 31, 2014 at 6:47 am

    #1685037

    interesting; so you need to find multiple substrings that are like 'd|[0-9][0-9][0-9][0-9][0-9]' ie '(d|41519)'

    here's a cte of his post for the adventurous;

    i'm trying a lazy DelimitedSplit8k cross apply to see if i can get it to work, but it's not presentable yet.

    ;WITH MyCTE([clndr_id],[clndr_data])

    AS

    (

    SELECT '178','(0||CalendarData()((0||DaysOfWeek()((0||1()())(0||2()((0||0(f|16:00|s|08:00)())))(0||3()((0||0(f|16:00|s|08:00)())))(0||4()((0||0(f|16:00|s|08:00)())))(0||5()((0||0(f|16:00|s|08:00)())))(0||6()((0||0(f|16:00|s|08:00)())))(0||7()())))(0||Exceptions()((0||0(d|41519)())(0||1(d|41155)())(0||2(d|40364)())(0||3(d|40910)())(0||4(d|41633)())(0||5(d|41094)())(0||6(d|40427)())(0||7(d|40536)())(0||8(d|41235)())(0||9(d|40179)())(0||10(d|40903)())(0||11(d|40543)())(0||12(d|40791)())(0||13(d|41421)())(0||14(d|40728)())(0||15(d|41275)())(0||16(d|41606)())(0||17(d|40329)())(0||18(d|41459)())(0||19(d|40871)())(0||20(d|40507)())(0||21(d|41057)())(0||22(d|40693)())(0||23(d|41268)())))))' UNION ALL

    SELECT '179','(0||CalendarData()( (0||DaysOfWeek()( (0||1()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||2()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||3()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||4()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||5()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||6()()) (0||7()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))))) (0||VIEW(ShowTotal|N)()) (0||Exceptions()( (0||0(d|36525)()) (0||1(d|36675)()) (0||2(d|36711)()) (0||3(d|36773)()) (0||4(d|36853)()) (0||5(d|36854)()) (0||6(d|36885)()) (0||7(d|36892)()) (0||8(d|37039)()) (0||9(d|37076)()) (0||10(d|37137)()) (0||11(d|37217)()) (0||12(d|37218)()) (0||13(d|37250)()) (0||14(d|37257)()) (0||15(d|37441)()) (0||16(d|37615)()) (0||17(d|37622)()) (0||18(d|37806)()) (0||19(d|37980)()) (0||20(d|37987)()) (0||21(d|38173)()) (0||22(d|38345)()) (0||23(d|38352)()) (0||24(d|38537)()) (0||25(d|38712)()) (0||26(d|38719)()) (0||27(d|38902)()) (0||28(d|39076)()) (0||29(d|39083)()) (0||30(d|39267)()) (0||31(d|39441)()) (0||32(d|39448)()) (0||33(d|39633)()) (0||34(d|39807)()) (0||35(d|39814)()) (0||36(d|39997)()) (0||37(d|40172)()) (0||38(d|40179)()) (0||39(d|40364)()) (0||40(d|40536)()) (0||41(d|41164)()) (0||42(d|41216)()) (0||43(d|41237)()) (0||44(d|41238)())))))'

    )

    SELECT * FROM MyCTE

    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!

  • January 31, 2014 at 6:57 am

    #1685041

    ok i got a lot more data than the ten or so rows you displayed, but this seems to do it.

    you might need to grab a vopy of DelimitedSplit8K

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    ;WITH MyCTE([clndr_id],[clndr_data])

    AS

    (

    SELECT '178','(0||CalendarData()((0||DaysOfWeek()((0||1()())(0||2()((0||0(f|16:00|s|08:00)())))(0||3()((0||0(f|16:00|s|08:00)())))(0||4()((0||0(f|16:00|s|08:00)())))(0||5()((0||0(f|16:00|s|08:00)())))(0||6()((0||0(f|16:00|s|08:00)())))(0||7()())))(0||Exceptions()((0||0(d|41519)())(0||1(d|41155)())(0||2(d|40364)())(0||3(d|40910)())(0||4(d|41633)())(0||5(d|41094)())(0||6(d|40427)())(0||7(d|40536)())(0||8(d|41235)())(0||9(d|40179)())(0||10(d|40903)())(0||11(d|40543)())(0||12(d|40791)())(0||13(d|41421)())(0||14(d|40728)())(0||15(d|41275)())(0||16(d|41606)())(0||17(d|40329)())(0||18(d|41459)())(0||19(d|40871)())(0||20(d|40507)())(0||21(d|41057)())(0||22(d|40693)())(0||23(d|41268)())))))' UNION ALL

    SELECT '179','(0||CalendarData()( (0||DaysOfWeek()( (0||1()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||2()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||3()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||4()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||5()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))) (0||6()()) (0||7()( (0||0(s|08:00|f|12:00)()) (0||1(s|13:00|f|17:00)()))))) (0||VIEW(ShowTotal|N)()) (0||Exceptions()( (0||0(d|36525)()) (0||1(d|36675)()) (0||2(d|36711)()) (0||3(d|36773)()) (0||4(d|36853)()) (0||5(d|36854)()) (0||6(d|36885)()) (0||7(d|36892)()) (0||8(d|37039)()) (0||9(d|37076)()) (0||10(d|37137)()) (0||11(d|37217)()) (0||12(d|37218)()) (0||13(d|37250)()) (0||14(d|37257)()) (0||15(d|37441)()) (0||16(d|37615)()) (0||17(d|37622)()) (0||18(d|37806)()) (0||19(d|37980)()) (0||20(d|37987)()) (0||21(d|38173)()) (0||22(d|38345)()) (0||23(d|38352)()) (0||24(d|38537)()) (0||25(d|38712)()) (0||26(d|38719)()) (0||27(d|38902)()) (0||28(d|39076)()) (0||29(d|39083)()) (0||30(d|39267)()) (0||31(d|39441)()) (0||32(d|39448)()) (0||33(d|39633)()) (0||34(d|39807)()) (0||35(d|39814)()) (0||36(d|39997)()) (0||37(d|40172)()) (0||38(d|40179)()) (0||39(d|40364)()) (0||40(d|40536)()) (0||41(d|41164)()) (0||42(d|41216)()) (0||43(d|41237)()) (0||44(d|41238)())))))'

    )

    SELECT [clndr_id], SUBSTRING(ThePipe.Item,3,5),* FROM MyCTE

    CROSS APPLY(SELECT Item From dbo.DelimitedSplit8K([clndr_data],'(') )ThePipe

    WHERE ThePipe.Item LIKE 'd|[0-9][0-9][0-9][0-9][0-9])%'

    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!

  • January 31, 2014 at 7:37 am

    #1685053

    Awesome

    Thanks very very much Lowell

  • January 31, 2014 at 9:18 am

    #1685102

    Awesome indeed. That's a very cool solution.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply