[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

Parse SQL Text Field

I have a text field in SQL that I need to parse.  The filed is notes entered by an employee.  Each note starts with [employeeid].  I want to find the last note entered.  See the code section below for example.  I would like to see:

[david] note 4

returned as the parsed string.
[bob] note 1 [bob] note 2 [john] note 3 [david] note 4

Open in new window

0
r270ba
Asked:
r270ba
  • 14
  • 11
  • 2
  • +2
2 Solutions
 
mrjoltcolaCommented:
You should probably consider adding regex support to your db. With 2005 you need to load in a CLR stored proc. http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Then you can do something like:

select dbo.RegexMatch( notes, N'(\[\w+\].*)$' ) from employee
0
 
radcaesarCommented:
Use PATINDEX

Search the string in reverse for first occurrence of [

pick the Whole (until end of string)
0
 
r270baAuthor Commented:
radceasar could you give me an example? Whole does not seem to be a function in SQL
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
r270baAuthor Commented:
also data type TEXT does not work with REVERSE()
0
 
Ron MalmsteadInformation Services ManagerCommented:
How about this...

declare @@test varchar(100)
SET @@test = '[bob] note 1 [bob] note 2 [john] note 3 [david] note 4 '
select Ltrim(rtrim(Replace(Reverse(LEFT(REVERSE(@@test),charindex(']',REVERSE(@@test)))),']','')))
0
 
r270baAuthor Commented:
xuserx2000 ... close but I want [david] to show as well.  all i get is note 4
0
 
Ephraim WangoyaCommented:

SELECT REVERSE(LEFT(REVERSE(NOTES), CHARINDEX(']', REVERSE(NOTES), 1)-1))
FROM TABLE1
0
 
r270baAuthor Commented:
ewanqoy, I get the same results as xuserx2000.  I would like to see the [david] note 4 instead of just note 4
0
 
Ephraim WangoyaCommented:

TRY

SELECT REVERSE(LEFT(REVERSE(NOTES), CHARINDEX('[', REVERSE(NOTES), 1)))
FROM TABLE1
0
 
r270baAuthor Commented:
Perfect!  Thanks guys!!!
0
 
r270baAuthor Commented:
Actually, now that I put it in the actual TEXT field instead of the varchar(100) that xuserx used to test, I get the following error:

Argument data type text is invalid for argument 1 of reverse function.
0
 
Ron MalmsteadInformation Services ManagerCommented:
Data type TEXT is very limiting.

Consider changing the data type if possible.
0
 
r270baAuthor Commented:
I cannot change.  Proprietary db...

Any other suggestions?
0
 
Ron MalmsteadInformation Services ManagerCommented:
select '[' + Ltrim(rtrim(Replace(Reverse(LEFT(REVERSE(Field1),charindex('[',REVERSE(Field1)))),'[','')))
 from(SELECT TOP 1 CAST(textfield as varchar(100)) as Field1 from Table1) temptbl
0
 
Ron MalmsteadInformation Services ManagerCommented:
..you can remove TOP 1
0
 
Ron MalmsteadInformation Services ManagerCommented:
0
 
r270baAuthor Commented:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.
0
 
r270baAuthor Commented:
wait...think i figured it out...brb
0
 
r270baAuthor Commented:
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SELECT  [last_note] = ( select  '['
                                + Ltrim(rtrim(Replace(Reverse(LEFT(REVERSE(Field1), charindex('[', REVERSE(Field1)))),
                                                      '[', '')))
                        from    ( SELECT    CAST(Notes AS VARCHAR(100)) as Field1
                                  from      dbo.tspvSalesDocument
                                ) temptbl1 
                      ),
        dbo.tspvSalesDocument.Sales_Doc_Num
FROM    dbo.tspvSalesDocument

Open in new window

0
 
Ron MalmsteadInformation Services ManagerCommented:
SELECT  [last_note] =
'[' + Ltrim(rtrim(Replace(Reverse(LEFT(REVERSE(cast(Notes as varchar(100))), charindex('[', REVERSE(cast(Notes as varchar(100)))))),'[', '')))

,dbo.tspvSalesDocument.Sales_Doc_Num
FROM    dbo.tspvSalesDocument
0
 
Ron MalmsteadInformation Services ManagerCommented:
..be advised, if Text field becomes greater than 100, i'm fairly certain it will break this or return an incomplete string.

In the even that happens, you may want to cut the var to only deal with the last 100 number of characters before CAST into varchar....or whatever number you decide is the limit for a "note" in your system.
0
 
r270baAuthor Commented:
Wow...thanks so much xuserx2000!  This works.  I actually modified varchar(100) to be varchar(7000) and it works fine.  Do you see a problem with this?
0
 
Ron MalmsteadInformation Services ManagerCommented:
There should be no problem unless you go over that amount, which is unlikely.  It's just that TEXT type holds more than varchar datatype.

You could probably work around this by using a stored procedure cut the TEXT, only dealing with the last portion of the field and store the result to a local variable before using it in the select.

It's just that TEXT field type is variable-length non-unicode data with a maximum length of 231 - 1 (2,147,483,647) characters.

Varchar(MAX) ...is 8000

You see the dilema ?
0
 
Ron MalmsteadInformation Services ManagerCommented:
You might consider creating a  user defined function to do that as well.

0
 
r270baAuthor Commented:
Ok great...I really dont' see us going over that.  Will it just error out if it does?
0
 
Ron MalmsteadInformation Services ManagerCommented:
I'm not sure if it will error out, or simply only return the first 8000 when casting to varchar(max)

Either way, I'm positive it won't be the desired result.

This could be part of the reason you can't do a "reverse()" on a text field.
0
 
r270baAuthor Commented:
Ok I think I can live within those parameters!  You have been more than helpful.  I wish I could I switch the points back around and give you the 350.
0
 
Ron MalmsteadInformation Services ManagerCommented:
nah...lol,   It's no problem.
0
 
r270baAuthor Commented:
Well thanks for your help!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 14
  • 11
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now