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,15 8,159,160,161,1 62,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 4705 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,15 0,151,154,155,1 58,159,160,161, 162,163,165,166 ,167,168,169,17 0,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 take a laptop offsite and still have
access, (even if read only) to the main data file (to be able to add records
in 1 table would also be useful).
I would like to be able to get the user to press a command button which
|
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 a dynamic array (similar to ReDim a(2)). I have a gut feeling
it does. Opinions?
Dim a() as String
a = Split("part1|part2|part3", "|")
' code that uses a goes here
Erase a
|
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 System_String. I don't see a
difference in the previous code and what I have now. Can anyone find where I
went wrong? Thanks a bunch!
//Open new stream reader and writer to read in file
//And write to a new file.
StreamWriter swa=new...
|
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, that the split method and the
regex.replace method was better than the string.replace method and replace
function. I did not believe that.
|
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...
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 without success.
for example if the number is 123 456 : i would like to have some random
strings like theese :
(12 * 10 000) + (345 * 10) + (6*1)
or
(123*1 000)+(4*100)+(5*10)+(6*1)
etc...
|
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 response.write (because
it does not split) is:
Microsoft VBScript runtime error '800a0009'
Subscript out of range: ''
/get_item.asp, line 55
|
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 breaks.
In other words, if my memo field is 3 paragraphs long and there
is only space on a report page for 2 paragraphs, rather than
putting 2 paragraphs on one page and the last one on the next
page, all 3 paragraphs are moved to the next 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 FE/BE. The tables link ok and everything works ok EXCEPT this function. When i open the form it gives me a underlining line (iReturn = ctl.Parent.PageIndex IN THE PARENTNUMBER function in BOLD) and indicating that iReturn=0. I am new with access and...
|
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 to be sent as individual SMS.
I am posting the function here in case its useful for someone.
Regards
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
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 can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |