CREATE FUNCTION [dbo].[Split]
RETURNS @t TABLE
id int identity(1,1),
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
r.value('.','varchar(15)') as item
from @xml.nodes('//root/r') as records(r)
Then cross apply to return your desired column. eg.
declare @tmp table (value varchar(100))
insert into @tmp values ('ASD654-100-CCCEEE4444-200-')
insert into @tmp values ('QWEQWEQ-2000-JJJLLL-20-PPPBBMMNN-600-')
cross apply dbo.split(value,'-')
where id = 3
I'm very new to SQL Server 2008, and need a bit of help manipulating numbers.
I've got a int column holding numbers up to 11 digits. I need to do the following manipulation and use it in a SQL query. Basically, the first digit is transfered to the 3rd position on the right side.
The values are variable, there are even records with only a value of 1.
i have one table(EMPLOYEE), in that columns are(Emp_Id,First_Name,Last_Name,Gender,Mobile_No,Email_id,Blood_Group);
in my front end page having 4 text boxies.that is emp_id,emp_name,mobile_no,blood_group.(here search buttion),
whenever we give id( or) name (or )mbno( or) bloodgroup(or) any combinations then result come with (Emp_Id,First_Name,Last_Name,Gender,Mobile_No,Email_id,Blood_Group)display.
i try some code but result is not coming.code is following
set str ="select Emp_Id,First_Name,Last_Name,Gender,Mobile_No,Email_id,Blood_Group from Employee where ";
if(empid!= '') then
set str= Concat(str ,"Emp_ID like '%", empid ,"%'and ");
set str= Concat(str , "First_Name like '%" , empname ,"%'and ");
set str= Concat(str , "Mobile_No like '%" , mobileno ,"%'and ");
set str= Concat(str ,"Blood_Group like '" , bloodgroup ,"%' ");
set str1= (SUBSTR(str,-6));
set str2 = (SUBSTR(str,-4));
set str= replace(str,'where','');
set str= replace(str,' and','');
Little help with returning both the name and the current identity for all tables? If a table doesn't have any rows, instead of the current identity, I'd like to return 0. The database will be in single user mode during the query if that helps.
The following query is close but it still returns 1 for tables that don't have any rows. Somehow the CASE expression isn't right.
Thanks in advance.
WHEN COUNT(TABLE_NAME) = 0 THEN
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
TABLE_TYPE = 'BASE TABLE'
SELECT CASE <br />
WHEN COUNT(TABLE_NAME) = 0 THEN 0<br />
WHEN COUNT(TABLE_NAME) > 0 THEN IDENT_CURRENT(TABLE_NAME)<br />
FROM INFORMATION_SCHEMA.TABLES<br />
WHERE TABLE_TYPE = 'BASE TABLE'<br />
GROUP BY<br />
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
Maybe I'm missing something here. How can that ever return 0?
If I have understood it correctly, this is what the query will do:
Select the rows from TABLES with type "BASE TABLE"
Group them by TABLE_NAME
Counts the number of rows for each TABLE_NAME
If the count is 0, return 0
If the count is not 0, return the identity of the table
But, surely the only way COUNT(TABLE_NAME) can be 0 is if there are no rows in TABLES with this TABLE_NAME and type "BASE TABLE". In which case, this query would never pick up that table name, so it would never appear in the result set. So for any TABLE_NAME that this query picks up, the count will always be greater than 0.
I have aproblem with my websit veiwing arabic data, when I importing data from csv file into web host sqlserver database the data in tables appears like ??????? and squares also in web pages, for notes the arabic field data type is nvarchar max and collation is arabic language .
so If any body have the answer please help me fast
But how are you importing the data. What encoding are you using when you read the data in? Just because the database is storing these values doesn't mean that the problem is at the database end - it's entirely possible that the problem is at the data population end.
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Hi, am designing a database that expedite collection of data from the insurance companies by insurance body. members are required to upload daily transaction in xml format.the insurance policy no is the primary key but other information can change. the data will be query by users. should i store the data as xml datatype or in split the column as use cdc to audit the canges or plit the column with history table . like below
I'd also add to the above comment to create a RealProperty view that queries on only those columns that deal with RealProperty and like wise for the HomeOwner data as well. Just make sure that each view uses the index fields defined at the table level and everything should work fine.
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
I would not count on that. Besides, as a developer, you would be better off learning to handle the more general situation sooner rather than later.
I would take a slightly different tack and either have the RealProperty table refer to the HomeOwner table (as it's more common for one owner to own multiple properties) or (more likely) allow many-to-many relationships via a third table. But that's just me.
I would not use a trigger. In the DAL, I would have a method to add a property and a method to add an owner -- and another method to add a relationship. In the API I would have a method that calls both (all three) and handles transactioning.
I would also use SQL Server .
Edit: Fixed relationship .
Edit 2: Oh, yeah, you had it that way...
modified on Monday, October 18, 2010 3:20 PM
Last Visit: 31-Dec-99 18:00 Last Update: 21-Sep-23 6:58