I have a variable called @Joins, datatype is nvarchar(Max). Issue is I have a long string as follows:
27,30,35,43,68,144,145,146,150,151,154,155,158,159 ,160,161,162,163,165,166........it's around 50,000 numbers. These are all id's and I need to process something with this id's. I am getting this id's in a variable called @Joins. However, while printing this variable, the data (ids) are truncating and getting only very few even though the data type is nvarchar(Max). How can I split this into two or three variables? Please help.
Thanks
mram.
4 4694 Delerna 1,134
Recognized Expert Top Contributor -
set @PortionSize=len(@Joins)/3
-
set @LeftSide=left(@Joins,@PortionSize)
-
set @Middle=substring(@Joins,@PortionSize+1,@PortionSize-1)
-
set @RightSide=right(@Joins,@PortionSize)
-
You will probably need to make some adjustments so that the same character doesn't get picked up into multipe variables and also that a number doesn't get split into two (use CharIndex() to find commas)
I will let you work that out.
How should I avoid the number split into two? Since I am not much into sql server, can you please help me that? I am getting now as follows:
LeftSide -dbo.SplitIds( '27,30,35,43,68,144,145,146,150,151,154,155,158,15 9,160,161,162,163,165,166,167,168,169,170,1
71,
Middle -,1302,1303,1304,1305,1306,1307,1308,1309,1310,1311 ,1313,1314,1315,1316,1317,1318,1319,1320,1321,
How to do with charindex function?
Thanks
Delerna 1,134
Recognized Expert Top Contributor
CharIndex returns the position within a string of another string.
Check SQL's help documents, it is pretty clear.
You can use that on @middle to find the position of the first comma and use that to adjust @PortionSize. Maybe you need 2 @PortionSize variables. 1 for the left side and 1 for the right side split positions
I am trying to not give you the answer here but rather ideas so you can find the answer for youself.
Think upon it and have a go, if you get stuck, post your code and I, or someone, will help
Presenting you 2 examples Solution 1(with recursive CTE) - declare @str as nvarchar(max)
-
declare @delimiter as char(1)
-
set @delimiter = ','
-
set @str = 'India,USA,Canada,Australia,Bhutan' -- original data
-
set @str = @delimiter + @str + @delimiter
-
-
;with num_cte as
-
(
-
select 1 as rn
-
union all
-
select rn +1 as rn
-
from num_cte
-
where rn <= len(@str)
-
)
-
, get_delimiter_pos_cte as
-
(
-
select
-
ROW_NUMBER() OVER (ORDER BY rn) as rowid,
-
rn as delimiterpos
-
from num_cte
-
cross apply( select substring(@str,rn,1) AS chars) splittedchars
-
where chars = @delimiter
-
)
-
-
select substring(@str,a.delimiterpos+1 ,c2.delimiterpos - a.delimiterpos - 1) as Countries
-
from get_delimiter_pos_cte a
-
inner join get_delimiter_pos_cte c2 on c2.rowid = a.rowid+1
-
option(maxrecursion 0)
Solution 2(with XQuery) - DECLARE @xml as xml,@str as nvarchar(max),@delimiter as varchar(10)
-
SET @str='India,USA,Canada,Australia,Bhutan'
-
SET @delimiter =','
-
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
-
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)
This methods will work in SQL SERVER 2005 & above.
Hope this helps
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Sam |
last post by:
I have developed a small database which is setup with the front end and
backend files.
There are 12 linked tables withe the datafile located on our server.
Now the boss wants to be able to...
|
by: Tom van Stiphout |
last post by:
I'm about to write a function like below, which I'm going to call a
lot of times. So I care about possible memory leaks.
I think whether I should use Erase or not depends on whether Split
creates...
|
by: Christine |
last post by:
My code has a split function that should split the text file of numbers.
I've run this in previous programs as it is here and it worked, but now it
wont work for some reason and returns...
|
by: Cor |
last post by:
Hi Newsgroup,
I have given an answer in this newsgroup about a "Replace".
There came an answer on that I did not understand, so I have done some
tests.
I got the idea that someone said,...
|
by: Ben |
last post by:
Hi
I am creating a dynamic function to return a two dimensional array from a
delimeted string.
The delimited string is like:
field1...field2...field3...
field1...field2...field3......
| |
by: Pascal |
last post by:
hello and soory for my english
here is the query :"how to split a string in a random way"
I try my first shot in vb 2005 express and would like to split a number in
several pieces in a random way...
|
by: vunet.us |
last post by:
Hello,
I use XMLHTTP to get an HTML of another page. Then, I need to cut some
middle part of that HTML string but I have problems doing it (see note
in caps below). The error I have generated at...
|
by: kaplan.gillian |
last post by:
Hi everyone,
I currently have an Access database that includes quite a few
long memo fields. When I create a report of my data, Access
does not allow the memo fields to be split with the page...
|
by: Gilberto |
last post by:
Hello,
I have a couple of forms using the code to FIND AS YOU TYPE from Allen Browne (http://allenbrowne.com/AppFindAsUType.html). It worked PERFECTLY until yesterday when i splitted the db into...
|
by: John |
last post by:
Hi
I have written a function to split a string into sub strings of a given
fixed max length. This is useful for example in breaking a long message into
multiple strings of up to 160 characters...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |