September 29, 2023 at 2:10 am
Hello,
I am trying to split data, looks like there is some special character or white space that is not letting me to split.
i try to split with space as delimiter even though there is space between two A2339 MC it will not split.
-- test date
DECLARE @TEST_STR TABLE
(
TEST_STRING NVARCHAR(255) NOT NULL
);
INSERT INTO @TEST_STR(TEST_STRING)
VALUES
(N'A2339MC ab Annual report131.39 131.39 00
')
,(N'44287QC New abc 65250.00 00250.00
')
--expected o/p should be
select 'A2339','MC ab Annual report','131.39','131.39','0','0'
union select '44287','QC New abc 65','250.00','0','0','250'
September 29, 2023 at 10:52 am
I'm afriad this won't be possible with the data that you have. There is no way to see that some spaces are within fields and some between fields. Maybe when you output the data from its original location you could get the fields quoted so you at least have:
"A2339""MC ab Annual report""131......
September 29, 2023 at 2:24 pm
My opinion, this is a horrible way to store your data. Why wouldn't you store the data inputs as their own bits of data?
What I mean is why would your table have 1 column of NVARCHAR rather than having multiple columns, one per possible input value?
Now, ignoring that problem, you say there is a space between "A2339" and "MC...", but in your code sample there doesn't appear to be one. Is it possible that it is NOT a space character but something else that looks like a space (such as the ASCII value NBSP)? If you can determine what character exists there that ONLY exists there, splitting should be trivial to do. What I'd do is select the 6th character in the first string and see if it is "M" or if it is something else. If it is something else, then use the function UNICODE (as it is NVARCHAR, you would use ASCII if it was VARCHAR or CHAR) and see what number comes back. Then try splitting the string based on that value.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply