SQL String Split With | Pipe

Dec 15, 2006

I've got a dilemma here. I'm currently reading records into EasyListBox (ELB) dropdownlist. The criteria in the dropdownlist is based on the select from another ELB dropdownlist. The problem is that the data contains rows with pipes that serve as separters. For example, one of the options that comes back now might be 123 | 456 | 789. I need to be able to make each of them its own row in the dropdownlist. Can anyone advice? Most records don't have any pipes and come throught fine, but some dont.

My code below: 

Dim sAT As String = Replace(elbAttribute.SelectedValue, "'", "''") 

Dim adapter As New SQLDataAdapter("Select Feature_Type As Attribute_Name, FEATURE_TEXT_VALUE as Attribute_Value, (select distinct FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID from FILTER_PROFILES, FILTER_FAMILY_FEATURE_EXCLUDED, FAMILY_ALLOWED_FEATURE_TYPES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FILTER_PROFILE_ID And FAMILY_ALLOWED_FEATURE_TYPES.FAMILY_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FAMILY_ID And FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID = SHARED_FEATURE_TYPES.FEATURE_TYPE_ID And (SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "')) As ExclusionFlag From SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES, PRODUCT_FEATURE_VALUES Where PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID = SHARED_FEATURE_VALUES.Feature_Value_ID And SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "' UNION Select distinct Column_Name As Attribute_Name, SMALL_TEXT_VALUE As Attribute_Value, (select FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID from FILTER_PROFILES, FILTER_ATTRIBUTES_EXCLUDED where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_ATTRIBUTES_EXCLUDED.FILTER_PROFILE_ID And FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID) As ExclusionFlag From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES, PRODUCTS Where PRODUCTS.PRODUCT_ID = EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID And not SMALL_TEXT_VALUE is null And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID > 1499 And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID And Column_Name = '" & sAT & "'", myConnection)
 Dim dt As New DataTable()
    adapter.Fill(dt)
 elbValue.DataSource = dt
    elbValue.DataBind() 

View 12 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Split Pipe Delimited String Into Two Columns

Mar 6, 2014

I have a single string "XYZ00001|Test_b|XYZ00002|Test_a|XYZ00003|Test_c" that will continue to grow over time.

Is there a way I can extract the values from the string into two separate columns?

XYZ0001 Test_b
XYZ0002 Test_a
XYZ0003 Test_c

View 5 Replies View Related

Transact SQL :: How To Split Comma And Pipe Separated From Single Set

Oct 21, 2015

I have an input parameter of an SP which value will be passed with different combinations with 2 seperators (comma and pipe)

Value to the parameter is like this :      '10|22|microsoft,20|25|sql,30|27|server,40|29|product'

I want output like this

Column1       Column2      Column3
10                   22              microsoft
20                   25              sql
30                   27              server
40                   29              product

Pipe separator is for column and comma separator is for row.

I know if its a single separator, it can be done with function but how to do if its 2 separators?

View 6 Replies View Related

SqlServer Connection String For JDBC When Using Named Pipe

Apr 27, 2008

Hi!

I'm writing a java application that queries sqlserver 2005 (and 2000). In case the sql browser service is stopped I need to use the named pipe property or the port property, right?
But how could the program know at runtime what is the port and what is the pipe name? And how should a connection string to sqlserver 2005 (and 2000) that includes named pipe, look like?

Thanks!

View 4 Replies View Related

Data Warehousing :: How To Skip PIPE (comes As Data) In PIPE Delimited Flat File

May 13, 2015

I have a problem with a PIPE "|" delimited flat file. I have a column "Description" in which we get a string in which we have PIPE "|" as data. How we can skip this and load it as a data into the column Description.

View 7 Replies View Related

How To Send Long String Using SqlContext.pipe.send(strString)?

Aug 15, 2006

Hi,



I have a string almost 11006 length.. when i try to send back from SQLCLR procedure

it says cannot send ..

here is Exception Text "Message length 11060 exceeds maximum length supported of 4000."

Max limit to send a string using pipe is 4000

How I can send a string which is large in size than 4000.



Thanks

View 6 Replies View Related

How To Split A String Using Sql

Feb 4, 2008

I have 5 dynamic rows each row consisting of 5 checkboxes & 5 dropdowns.I am concatenating the values of each controls in a row using a wildcard charater "~" and each row i am concatenating using "|".The complete string is then assigned to one hidden field and passed as sql parameter to the backend.

Please help in writing the split function to get the values of each checkboxes and dropdowns from the string in order to save them in separate columns.

Thanks

View 3 Replies View Related

Split A String In Sql

Nov 28, 2002

Hi I need a stored procedure in SQL that will split a comma separated variable passed to it
select a name for each value and return a recordset. Any pointers greatfully received.
First attempt is dreadfully slow as I am opening recordsets each time

Function func_getFood()
Dim rsfoodsql
Dim foodoutput
for x=1 to ubound(masterfoodarray)-2
set rsfoodsql= objconn.execute ("select foodname from tbl"&language&"food where foodID='"& masterfoodarray(x) &"'")
if not rsfoodsql.eof then
foodoutput=rsfoodsql("foodname")
if not foodoutput="" then
response.write foodoutput&"<BR>"
end if
end if
next
End Function

Hope someone can help,
cheers

View 2 Replies View Related

Split A String

Oct 29, 2007

Hi
I have this string which might have a hyphen in it "-"
What I want to do is
if I get a hyphen then take all characters after hyphen
else take only all the characters starting from the 5th position of the string
How can this be achieved?

View 7 Replies View Related

How To Split The String

Jun 4, 2008

I am having City, State i need to split these two string with comma separated and to search based on the city and state how to write the select query

any one know help me

Regards,
Prabu R

View 1 Replies View Related

Split String Using Dot

Apr 8, 2015

I have data like 'US.USA.Florida.Miami'. I need to split this string as below

Continent Country State Location
=========== ========= ========= ============
US USA Florida Miami

Delimiter is dot '.'.

Is there any chance to do this using the sql server built in functions.

View 4 Replies View Related

How To Split A Delimeted String...

May 9, 2008

 hi,
i have a simple question, i have a sql server table named "Restaurant" and a field "Cuisines",
the values in the field Cuisines contains delimeted string. (e.g. "C1,C2,C3...C10" ,maximum to 10 items or less)
now, i want to split the values from Cuisines ("C1,C2,C3...C10") and passed all of them to a SP parameter.
e.g
@item0 nvarchar = "C1",@item1 nvarchar = "C2",@item2 nvarchar = "C3",@item3 nvarchar = "C4",@item4 nvarchar = "C5",@item5 nvarchar = "C6",@item6 nvarchar = "C7",@item7 nvarchar = "C8",@item8 nvarchar = "C9",@item9 nvarchar = "C10"
 if the field "Cuisines" contains less than 10 items, the value of the remaining parameters is null.
any response will be appreciated. thanks.
 
 

View 5 Replies View Related

Split Command In String

Feb 6, 2008

hi all, i am still a newbie in sql

i got a problem with my query

for example:
-------------------------------
DECLARE @LongString VARCHAR(10)
@LongString = "Hello-Word"
------------------------
i want to change the LongString value by removing the "-" however i i need to identify if the @longString got "-" and delete it if it found how do i do that in sql

Thanks a lot guys

arifliminto86

View 3 Replies View Related

How To Split A Comma In A String

Mar 11, 2008

I have a string

@string = 'abc,def,ghi'

Now I have to display

abc
def
ghi

I have to separate a comma in that
Is there an function like split in sql

plz help me
Thanks in Advance

Suresh Kumar

View 5 Replies View Related

Sql Charindex Split String

Jul 23, 2005

HelloI am quite hopeless and of course a newbe.The situation: Sql2k / queryI would like it ot break down the following string:2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094Whereby:Year = '2004 Inventory'plant= 'Ex.Plant Farm1'cut = '1st Cut'grade = 'Premium'lot# = '0094'It is always seperate by ':', but it can be 5 fields or 4 or 3 and sooncode to create the view:CREATE VIEW dbo.TESTASSELECT FullName, LEFT(FullName, CHARINDEX(':', FullName + ':') -1) AS year, CASE WHEN LEN(FullName) - LEN(REPLACE(FullName, ':', ''))[color=blue]> 0 THEN LTRIM(SUBSTRING(FullName,[/color]CHARINDEX(':', FullName) + 1, CHARINDEX(':', FullName + ':',CHARINDEX(':', Fullname) + 1) - CHARINDEX(':',FullName) - 1)) ELSE NULL END AS Plant, CASEWHEN LEN(FullName) - LEN(REPLACE(FullName, ':', '')) > 1 THENLTRIM(SUBSTRING(FullName,CHARINDEX(':', FullName + ':', CHARINDEX(':',FullName) + 1) + 1, CHARINDEX(':', FullName + ':', CHARINDEX(':',Fullname) + 1) - CHARINDEX(':',FullName+':') - 1)) ELSE NULL END AS [Cut]FROM dbo.ItemInventoryCan anyone help me with this? I am stuck half the way and get for cutthe rest of the string: '1st Cut:Premium:0094'Thanks!

View 5 Replies View Related

Split The String Into Columns

Feb 6, 2008




I have a table called products with the values like

ProductId ProductName
10 A
20 D,E,F,G
30 B,C
40 H,I,J

I need to display each productid's with

ProductId ProductName
10 A

20 D
20 E
20 F
20 G
30 B
30 C
40 H
40 I
40 J

I will be appreciated if you can send me the code.

Thanks,
Mears

View 5 Replies View Related

Split String Using Delimiter

Oct 26, 2006

Hi,

I get a string whihc looks like 'Q306/Q406 Version1/Current/Q108 Version2'

I need to split the above string and get each of those values... ' / ' delimiter

Can some one please help on this.

Thanks

View 7 Replies View Related

Split String And Insert Into Rows

Apr 4, 2014

I have a string stored in field v_images in table tbl_DealerFeed_temp. This string is split by the "|" character. I want to create a SQL query to grab this string, split it, and insert it into tbl_Vehicles_photos. I also need to pass the value d_id into the photos table:

tbl_Vehicles_photos:
vp_id, d_id, vp_image_name

The vp_id is the unique ID. I need to populate the d_id and vp_image_name field with my values.

View 1 Replies View Related

Transact SQL :: String Split After The Space

May 18, 2015

DECLARE @FullName        VARCHAR(100)
SET @FullName = 'Vauxhall Adam Rocks AIR Vauxhall'

SELECT LEFT(@FullName, NULLIF(CHARINDEX(' ', @FullName)  -1, -1)) AS [FirstName],
       RIGHT(@FullName, ISNULL(NULLIF(CHARINDEX(' ', REVERSE(@FullName)) - 1, -1), LEN(@FullName))) AS [LastName]

This is only gives first and last not first and middle 
 
DECLARE @FullName        VARCHAR(100)
SET @FullName = 'Vauxhall Adam Rocks AIR Vauxhall'
SELECT 
STUFF(@FullName,charindex(' ',SUBSTRING(@FullName,5,LEN(@FullName)))+5,LEN(@FullName),'') [Firstname1],
  STUFF(@FullName,1,charindex(' ',SUBSTRING(@FullName,5,LEN(@FullName)))+4,'') Lastname1

Not right as it gives 

Vauxhall 
Adam Rocks AIR Vauxhall

Ideally the result should be 

Vauxhall
Adam Rocks AIR 

View 6 Replies View Related

T-SQL (SS2K8) :: Split String To Show Only Customer

Aug 7, 2014

The data we are getting from our shipping company has the customer name and customer number attached.

so we could have.. declare @String varchar(25) = 'asdf - 10'
but we also have.. declare @String varchar(25) = 'asdf - jeik - 10'

So how do I strip off the " - 10", when the ending number is not the same number of char's (1,11,111,1111, ect)

I need to match this up with our customer table... on Customer Name.

View 9 Replies View Related

T-SQL (SS2K8) :: Possible To Split String To NICK And SERVERS

Jun 17, 2015

Can it be possible to split the below string to NICK and SERVERS. Query which provides two columns, first column data should be NICK and other SERVERS.

_BF-TYPE:NICK_ _BF-SUBSYSTEM:SERVERS_

View 9 Replies View Related

Split String And Insert Into Table Rows

Apr 17, 2014

I have a string which is delimited by commas:

Green,Red,Blue,Orange,Black,White

I need to create a SQL statement to read the string, split it at the "," character, and insert it into individual rows. I also need to insert an ID (the same for all split values).

For instance:

INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (1, 999, Green)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (2, 999, Red)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (3, 999, Blue)
etc...

I would like to be able to do this all in one statement so I don't have to break it out into individual SQL statements.

View 4 Replies View Related

Transact SQL :: Split A String And Store As First And Second Part

Jul 17, 2015

I am using SQL Server 2008. I have strings like this:

AB-123
CDW-32
declare @First_Part varchar(3)
declare @Second_Part varchar(5)

I want to split the string with delimiter '-' and store as first part and second part.

I saw few sample functions to split a string but these return table of values.

I simply want first part and second part.

In above examples context:

@First_Part = 'AB'
@Second_Part = '123'

Any simple way to do this?

View 6 Replies View Related

Transact SQL :: How To Split Comma Delimited String

Oct 8, 2008

I have a parameter called Id in my SP which will be of nvarchar data type and i'm going to get the multiple ids at a time seperated by commas in that parameter from the application. Now my requirement is to update a table with these comma seperated ids in seperate rows.

For example, if i have 2 parameters called Id1 and Id2. Id1 will contain only one value and will be of int data type and Id2 will be of nvarchar data type as i can get multiple ids delimited by a comma from the application.

Suppose Id1 = 1 and Id2 = '1,2,3,4'. Then I have to update id2 in the tables seperately like wherever Id1 is '1' i need to update Id2 column for 4 rows with the value 1, 2, 3, 4 respectively in different rows.
 
how can i do this in T-SQL? How can i split the data of parameter Id2 in 4 different rows?

View 24 Replies View Related

SPLIT A String Inside SSIS/T-SQL Code?

Feb 20, 2008



I have a RPC which gives me multiple - single record rows like


Robert|K|Half|TX|1123823|1423904 -- This is one such record that i get in 1 Column


Now I need to split the above record into 6 Fields and populate in my local DB
I know i could use any .NET Language and accomplish this. But Iam limited to using SSIS/T-SQL Proc's


Any ideas / directions?

View 6 Replies View Related

How Do I Split And Insert A Comma Delimited String To A Table?

Oct 13, 2004

I am passing in a string of dates, delimited by a comma.

So

01/01/04, 02/01/04, 03/01/04

etc

I would like to enter each of these values into a table via an INSERT stored procedure.

Does anyone have any code for this?

View 1 Replies View Related

Transact SQL :: Split Half Hour In Number From String

Jun 11, 2015

How I can split the half hour in number from this string?
 
20130329070000 (it's varchar)
Easy for the date
cast (left(20130329070000,8)as date)

I can achieve even the Half Hour

select
left(SUBSTRING('20130329070000',9,4),2)+':'+RIGHT(SUBSTRING('20130329070000',9,4),2)

But what I need is the Half Hour in numeric format, so 00:00

should be 1, 00:30 should be 22, 01:00
should be 3, 01.30 should be 4......23:30 should be 24.

I already did but I can't find the piece of code I used.

View 5 Replies View Related

Transact SQL :: Split String As Columns And Insert Into Table

Aug 20, 2015

I have a string ,want to split the values after every space as column value and insert them into a table 

 1306453 0 0 0 0 0

col1      col2  col3 col4  col5 col6
1306453    0         0       0         0       0

View 7 Replies View Related

Split A Column String-value Into Multiple Columns / Inserting In Another Table

Aug 14, 2012

I have a table with a string value, where all values are seperated by a space/blank. I now want to use SQL to split all the values and insert them into a different table, which then later will result in deleting the old table, as soon as I got all values out from it.

Old Table:

Code:
ID, StringValue

New Table:

Code:
ID, Value1, Value2
Do note: Value1 is INT, Value2 is of nvarchar, hence Value2 can contain spaces... I just need to split on the FIRST space, then convert index[0] to int, and store index[1] as it is.

I can split on all spaces and just Select them all and add them like so: SELECT t.val1 + ' ' + t.val2... If I cant find the first space that is... I mean, first 2-10 characters in the string can be integer, but does not have to be.Shall probably do it in code instead of SQL?Now I want to run a query that selects the StringValue from OldTable, splits the string by ' ' (a blank) and then inserts them into New Table.

Code:
SELECT CASE CHARINDEX(' ', OldTable.stringvalue, 1)
WHEN 0 THEN OldTable.stringvalue
ELSE SUBSTRING(OldTable.stringvalue, 1, CHARINDEX(' ', OldTable.stringvalue, 1) - 1)
END
AS FirstWord
FROM OldTable

Found an example using strange things like CHARINDEX..But issue still remains, because the first word is of integer, or it does not have to be...If it isn't, there is not "first value", and the whole string shall be passed into "value2".How to detect if the very first character is of integer type?

Code:
@declare firstDigit int
IF ISNUMERIC(SUBSTRING(@postal,2,1) AS int) = 1
set @firstDigit = CAST(SUBSTRING(@postal,2,1) AS int)
ELSE
set @firstDigit = -1

[code]....

View 2 Replies View Related

SQL Server 2012 :: Need To Split Numeric And Alphabetical Values From String

Jan 10, 2014

I need to split NUMERIC & ALPHABETICAL values from string.

for eg :-

1234heaven56-guy

output

123456 heaven-guy

View 3 Replies View Related

SQL Server 2008 :: Split Comma Separated String Into Columns?

Apr 24, 2015

Our front end saves all IP addresses used by a customer as a comma separated string, we need to analyse these to check for blocked IPs which are all stored in another table.

A LIKE statement comparing each string with the 100 or so excluded IPs will be very expensive so I'm thinking it would be less so to split out the comma separated values into tables.

The problem we have is that we never know how many IPs could be stored against a customer, so I'm guessing a function would be the way forward but this is the point I get stuck.

I can remove the 1st IP address into a new column and produce the new list ready for the next removal, also as part of this we would need to create new columns on the fly depending on how many IPs are in the column.

This needs to be repeated for each row

SELECT IP_List
, LEFT(IP_List, CHARINDEX(',', IP_List) - 1) AS IP_1
, REPLACE(IP_List, LEFT(IP_List, CHARINDEX(',', IP_List) +0), '') AS NewIPList1
FROM IpExclusionTest

Results:

IP_List
109.224.216.4,146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119
IP_1
109.224.216.4
NewIPList1
146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119

View 8 Replies View Related

SQL Server 2014 :: Query To Split String As Rows And Columns

Oct 19, 2015

I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.e.g string = "Param1 =3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters.
Currently am using the below function and getting the parameters by each in select statement as mentioned below.

select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1,
[dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2
CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex]
(
@CustomProp varchar(max),

[code]....

View 8 Replies View Related

SQL Server 2012 :: Split String Into Columns Based On Special Character

Dec 4, 2013

How to get the required result in SQL 2012

Create table DBInfo (Path varchar (500))
Insert into DBInfo values('/Data Sources')
Insert into DBInfo values('/Data Sources/SALES')
Insert into DBInfo values('/PRODUCTION')
Insert into DBInfo values('/PRODUCTION/SERVICE')
Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE')
Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')

My Expected Output

Column1,Column2,Column3
Data SourcesNullNull
Data SourcesSalesNull
PRODUCTIONNullNull
PRODUCTIONSERVICENull
PRODUCTIONSERVICEMAINTENANCE
PRODUCTIONSERVICELOGISTICS

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved