A table I'm working with has a varchar column containing a comma-delimited string of numbers, which match up to smallint codes in another table. I gather this is someone's implementation of a many-to-many relationship. :)
Using SQL Server 2008 and wondering if there's a special trick to using a string list of numbers with IN() URL...converting the string into a temp table[/url] but I'd just like to make sure there isn't a quicker, easier approach, or if that's it.
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
I am trying to Strip off the Numbers witha Delimited List and just retain the Name of the Persons. but unable to do it. is there any function or code to do that in SQL
932908` James Fleser,935992` Prakash Sinha
Stripping off Numbers for the Above and Just retain the Names..
Hi. I am searching for a reference with all error numbers an descriptionsfor ms sql odbc.Has anybody something for me?Thanks a lot.Greeting Jan Entzminger
I have data in a table Item_TB that I need to extract in a way that pulls out the distinct pax name and all the ticket numbers associated with the passenger per booking reference.
The data is:
Branch Folder ID Pax TktNo BookingRef HQ 123 1 Jim 4444 ABCDE HQ 123 2 Bob 5555 ABCDE HQ 123 3 Jim 6666 ABCDE HQ 123 4 Bob 7777 ABCDE HQ 124 1 Jenny 8888 FGHIJ HQ 124 2 Jenny 9999 FGHIJ HQ 124 3 Jenny 3333 FGHIJ
I somehow need to get a function to pull the data out for each booking ref like so
--BookingRef ABCDE Jim 4444/ 6666 Bob 5555 7777 --BookingRef FGHIJ Jenny 8888/ 9999/ 3333
I know I can get a simple function to return the all data, but I do not know how to only include the pax name once.
Say you have a table that has records with numbers sort of like lottery winning numbers, say:
TableWinners num1, num2, num3, num4, num5, num6 33 52 47 23 17 28 ... more records with similar structure.
Then you have another table with chosen numbers, same structure as above, TableGuesses.
How could you do the following comparisons between TableGuesses and TableWinners:
1. Compare a single record in TableGuesses to a single record in TableWinners to get a count of the number of numbers that match (kind of a typical lottery type of thing).
2. Compare a single record in TableGuessess to ALL records in TableWinners to see which record in TableWinners is the closest match to the selected record in TableGuesses.
I m working on MS SQL Server 2000. I am trying to pass a list of numbers to a stored procedure to be used with 'IN()' statement.
I was doing something like..
Create Procedure proc
(
@Items varchar(100) --- List of numbers ) AS Begin
Declare @SQL varchar(8000) Set @SQL = ' Select Query...... Where products IN (' + @items + ') ' ' Exec (@SQL)
This stored procedure is working fine, but when i m adding more required stuff to that, the size exceeds 8000, & it gives the error "Invalid operator for data type. Operator equals add, type equals text."
Have a need to let the user select the year and month to use for filtering results from an MDX query that populates a report.I think all I should need is a list of years and a Month name / number.What would the filter look like if say I had a Date dimension that included the year number in a 4-digit and month in a 2-digit number? In TSQL it would be something like ... where Month = @month AND Year = @year...How do I get a distinct list of member values for the month numbers from the Date dimension?
In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.
Say I have a string of Numbers eg. "1,2,3,4,5" and in order to split it I use split("1,2,3,4,5", ",") function, what would I get as an answer? My second question is, if I want to pass all 5 numbers in a sub report as if I am selecting from a multi-value parameter, how would I do that? I have tried several different ways of doing it and I still can't get it to work. Please help me...and feel free to email me back if somethign is not clear.
I have a SELECT statement, the result of which populates a datagrid. The first column has consecutive dates in it and I want to hyperlink each date to a seperate Javascript function (the Javascript is created on the fly and is unique for each date). I need a different function name for each function and so tried the date but "/" is not allowed in the Javasript function name. I think the easiest way will be to produce a new column with the date expresses ddmmyyyy, ddmmyy or some such unique number (but not dd/mm/yyyy). I tried :-
"CASE " & _ "WHEN t3.date = t3.date THEN (DAY(t3.Date) + MONTH(t3.Date) + YEAR(t3.Date)) ELSE NULL END AS [javaKey]
but this adds the year to the month to the day - not a unique result as 1/2/06 and 2/1/06 are the same.
I am just getting to grips with VB.Net (as an amature) but am a distinct beginner at SQL!
I have a column in a table which looks like below.
Column ------- AA123 D123 AXC1 QF23
I need to split this value into two part, Alphabets and numeric. How to do this using SQL query.My column value will not have mixed characters like A1D3,G32S,12F.It will always follow the ablve pattern mentioned above.
I've got a string of numbers, say 123456 (the actually number is 12 digitslong). I need to calculate the sum of each individual number in the stringof numbers, so in the example of 123456 the sum would be 21 (1+2+3+4+5+6).Thanks
I have a string and i want to get only the numbers from right.
For example if I have the string Like '123756zxfggr123456' then it will show me only 123456 or if i have the string like '4vbz67xfggr123dfd' then it will show me only 123 or if i have the string like '123756zxfgg43r5' then it will show me only 5.
I got a function where it gives me all the numbers in a string but I don't need that
CREATE FUNCTION dbo.udf_GetNumeric (@strAlphaNumeric VARCHAR(256)) RETURNS VARCHAR(256) AS BEGIN DECLARE @intAlpha INT
[Code] ....
If I ran the select statement it gives me the result 111123456 but i want only 123456 or if i select
SELECT dbo.udf_GetNumeric('111zxfggr6587fhhfkwee') AS 'Num' it will show me 6587.
Yeah, it's pretty simple. Maybe it'll help someone out.
-- USAGE: fn_extract_chars(string_to_search, 'letters' -or- 'numbers') CREATE FUNCTION fn_extract_chars (@x varchar(128), @y char(7)) RETURNS varchar(128) AS BEGIN DECLARE @chars varchar(128) DECLARE @pos int DECLARE @action varchar(32) SET @pos = 0 SET @chars = ''
IF @y = 'numbers' SET @action = '[0-9]' ELSE IF @y = 'letters' SET @action = '[a-zA-Z]'
WHILE @pos < (DATALENGTH(@x) + 1) BEGIN IF PATINDEX(@action,SUBSTRING(@x, @pos, 1)) > 0 BEGIN SET @chars = @chars + (SELECT SUBSTRING(@x, @pos, 1)) END SET @pos = @pos + 1 END RETURN(@chars) END
Hello, I'm having problems with a column of numeric string data coming from a tab delimited CSV file. When a number in the column is negative, it is expressed this way; 1,240.52-
The negative symbol occurs at the end of the numeric string. The destination column is in a SQL Server 2005 table. I've tried to change the data type of the table column, and three or four different data types in a data conversion task, but nothing has worked so far to bring the data over.
I'm trying to put conditional formatting on a field, that behaves as follows:
The data in the field is varchar, and sample data is either:
NULL 3.0 :0 11.7 :1 (these are ratios of a sort)
I want to evaluate the first 3 characters of the string as numbers.
Example: Mid(fieldvalue,1,3) = "3.0" or "11."
Any data that is greater than 1.99, I want to make the background dark red, anything else including nulls, zebra formatting. I have the following expression built so far and it appears to work, except when the value is null. If the value is null, it leaves the background color white.
This is the warning: [rsRuntimeErrorInExpression] The BackgroundColor expression for the text box "Asthma" contains an error: Input string was not in a correct format.
=iif( isnothing(Fields!Asthma.Value) ,(IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1")) ,(iif(mid(Fields!Asthma.Value,1,3)>1.99 ,"DarkRed" ,IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1"))))
My logic is, if the field is null, zebra format, if mid of the value is > 1.99, dark red, everything else zebra formatting. As I said, this seems to work except for nulls.
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Can anyone point me to a comprehensive list of the options available in connection strings? I know that a lot of the options are able to be set elsewhere, but I'd like to have a complete listing for interests sake.
I want to do something like this in a stored proc:------Create Procedure dbo.GetPatients@PatientIdList varchar(200) -- comma separated list of PatientIDsAsSelect *From PatientsWhere PatientId In (@PatientIdList)------I know the above won't work, but of course what I want is if@PatientIdList = '1,2,3' then I want Patient records with PatientIds1, 2, and 3 returned.It looks like the only way to do this is to build the SQL statement asa string within the stored procedure ... which pretty much defeats theusefulness of using precompiled sprocs as I understand it (better offbuilding a dynamic query against a View in that case).Thoughts?Joel Thornton ~ <groups@joelpt.eml.cc>
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
I have a list of ClassID that is stored based on users multi select on a listview
For example ClassID might contain
301 302 303 304
Now I need to find InstructorID where classID matches all the value in the above list.
I am using this query
Code: Dim assSQL = "Select InstructorID from ClassInstructors where ClassID = @P0" For i = 1 To classIDs.Count - 1 assSQL &= " UNION Select InstructorID from ClassInstructors where ClassID = @P" & i.ToString Next
[Code] ....
But the problem is the query is returning InstructorID where ClassID matches any of the ClassIDs. I want it to return Instructor ID where ClassID matches all of the ClassIDs in the string.
I am trying to create a Datazen query against a Microsoft SharePoint List. The Data View successfully returns records from the list; however, it appears that the Query String parameter is entirely ignored. I want to use the query string to grab only records with a certain status value.
So far I have attempted specifying a View and Filters:
All return the complete set of records in the list. I do not see a single example or comment online other than the claim that SharePoint List sources are supported. How I can specify a Query String value that will filter the list records?
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like: List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
