-4

I need to modify/cut some characters from my variable @txt

The variable is declared like this:

DECLARE @txt VARCHAR(MAX)

and @txt is a long string.

I need to cut and move this marked fragment in blue (screenshot)

variable_string

How can I do this? Could you please give me some advice that will help me?

enter image description here

Luke
  • 1
  • 4
  • Are you sure that's in a variable, and not inside a column? Otherwise, you're looking for some sort of `split` operation, which most RDBMSs don't have (for a variety of reasons), so you may be better off doing it at an application level, or just by hand (since it looks like a data-entry problem, more than a query issue). – Clockwork-Muse Jan 17 '17 at 23:40
  • I have this inside in @txt and inside the column too but i have no idea how can i cut this marked fragment and move to other table 'cut_table' – Luke Jan 17 '17 at 23:43
  • Not exactly sure what your goal is. Could you give us an example of the desired output? – Möoz Jan 17 '17 at 23:44
  • 1
    Please provide sample input and desired output, and through what mean you would like to achieve. – ydoow Jan 17 '17 at 23:46
  • at an application level i haven't found the reason yet so now i would like to correct only this – Luke Jan 17 '17 at 23:47
  • my goal is : write a procedure or a query to cut this blue piece of string from one table and after that move to second table for my information, thats it – Luke Jan 17 '17 at 23:53
  • ....ouch. Much better to figure out what's happening, and why. Better to spend the effort to fix the problem, and not the symptom. For instance, it's pretty obvious that you have a row of data getting munched, possibly permanently (it's unclear what happened to the rows). Which means your database has incorrect data. Depending on what this represents, that could be pretty important. Most likely, you're looking for a piece of code that loops or recurses while doing the string concatenation, probably with a `continue` or similar, and doesn't clear the accumulator variable. – Clockwork-Muse Jan 17 '17 at 23:56
  • the problem is complicated and first i need to collect incorrect date to analyse – Luke Jan 18 '17 at 00:05
  • Well, a variation on the standard [split string](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) function should get you started, although your real problem is going to be figuring out where the rows actually **stop**. Which is why I really recommend fixing the actual problem, and not the symptom. – Clockwork-Muse Jan 18 '17 at 00:49
  • `VARCHAR(MAX)` does not support _blue_ text. Is there some other way to determine what text should be removed, e.g. a pattern to search for? – HABO Jan 18 '17 at 04:08
  • it should be removed because the blue text is incorrect but the blue text should be moved to other table for my information – Luke Jan 19 '17 at 00:08
  • Why VARCHAR(MAX) does not support blue text? – Luke Jan 19 '17 at 09:11

1 Answers1

0

Use your data cleverly. We assume that values in 1st and 2nd fields (eg Corp and 111EF111) are variable length.

Therefore, we search for the - in the date, and then for the 3rd comma after it:

SELECT   CHARINDEX('-', @txt) + 15 AS Index_First_Date_Dash
       ,CHARINDEX(',',@txt, CHARINDEX('-', @txt) + 15 + 12) AS Index_3rd_Comma_After_Time
       ,SUBSTRING(@txt,1,CHARINDEX(',',@txt, CHARINDEX('-', @txt) + 15 + 12)) AS Extract

Screenshot showing string extraction

Serge
  • 3,986
  • 2
  • 17
  • 37
  • Hi, Nice, I think that using charindex is a good idea and maybe the one. Now I would like to create a smart procedure which if this procedure finds after 3rd comma the word Corp [that will be data-entry problem] cut this row to the CRLF before and simultaneously insert this to the new table . Is this clear? Can you help me with this? – Luke Jan 18 '17 at 20:02