Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Split string into table, separator can be more than 1 char

By Luigi Marinescu,

This function has been developed by me and my friend Mihai Ciurescu.

Let's suppose that you have a web page with a listbox (multiple selection) of States and you want to get all customers having properties within your selected States. So, your page will send to the SQL Server a list of all selected State_IDs.

Now, your list of IDs is like '123,43,5465,6788,1231' and you want to split and pivot them into a table like:

Your_Value
123
43
5465
6788
1231

Using this function now you can use its table-result to do an INNER JOIN with your_Address table (where you store the State_Id) ON .fn_SplitStringToTable.ItemData = your_Address.State_Id

This is what this

SELECT * FROM dbo.fn_SplitStringToTable('123,43,5465,6788,1231', ',')

returns

RowIndex FromPos ToPos ItemData
1 1 4 123
2 5 7 43
3 8 12 5465
4 13 17 6788
5 18 22 1231

Enjoy!

Luigi and Michael

 

 

Total article views: 1337 | Views in the last 30 days: 1
 
Related Articles
FORUM

Database in Recovery Pending State

Database in Recovery Pending State

FORUM

Table for Object with Multiple States

Multiple States for object/entity

FORUM

select count

select count

FORUM

where is the database 'state' is stored

where is the database 'state' is stored

FORUM

Error: 8355, Severity: 16, State 1

Error: 8355, Severity: 16, State 1

Tags
pivot    
separator    
split    
 
Contribute