|
wow... that code was very hard to understand..
i'm still a newbie here.
but thanks alot friend. it helps and i'll try... 
|
|
|
|
|
siang_wu_id wrote: that code was very hard to understand
May be, for big works probably we need to write massive code(at least bunch of code).
siang_wu_id wrote: i'm still a newbie here.
but thanks alot friend. it helps and i'll try...
It's really great.
|
|
|
|
|
Hi,
I suggest you create a split function
CREATE FUNCTION [dbo].[Split]
(
@delimited nvarchar(max),
@delimiter nvarchar(100))
RETURNS @t TABLE
(
id int identity(1,1),
val nvarchar(max))AS
BEGIN
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
select
r.value('.','varchar(15)') as item
from @xml.nodes('//root/r') as records(r)
RETURN
END
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-')
select val
from @tmp
cross apply dbo.split(value,'-')
where id = 3
Ryan
|
|
|
|
|
Hi All,
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.
12345678
23456178
or
1234
2134
The values are variable, there are even records with only a value of 1.
Thanks in advance.
|
|
|
|
|
ac011 wrote: Basically, the first digit is transfered to the 3rd position on the right side.
- Cast the column to a
VARCHAR
- Reararrange the characters using the string-functions described here[^]
I are Troll 
|
|
|
|
|
Hi,
How about...
<br />
declare @value varchar(11)<br />
set @value = cast(12345678 as varchar)<br />
select cast(substring(stuff(@value, len(@value)-1, 0, substring(@value,1,1)), 2, len(@value)) as int)<br />
Ryan
|
|
|
|
|
|
Hello
See SQL
update wp_postmeta set option_value = replace(option_value, ‘nkhba.net\wordpress’, ‘alamhamasat.net’);
In the field option_value of wp_postmeta the following text
a:6:{s:5:"width";i:350;s:6:"height";i:280;s:14:"hwstring_small";s:23:"height='96' width='120'";s:4:"file";s:90:"D:\hshome\c259998\nkhba.net\wordpress/wp-content/uploads/2009/10/ououousoouououusoouso.jpg";s:5:"sizes";a:2:{s:9:"thumbnail";a:3:{s:4:"file";s:33:"ououousoouououusoouso-150x150.jpg";s:5:"width";i:150;s:6:"height";i:150;}s:6:"medium";a:3:{s:4:"file";s:33:"ououousoouououusoouso-300x240.jpg";s:5:"width";i:300;s:6:"height";i:240;}}s:10:"image_meta";a:10:{s:8:"aperture";i ;s:6:"credit";s :"";s:6:"camera";s :"";s:7:"caption";s :"";s:17:"created_timestamp";i ;s:9:"copyright";s :"";s:12:"focal_length";i ;s:3:"iso";i ;s:13:"shutter_speed";i ;s:5:"title";s :"";}}<
When I run that query above, it should affect the bold string (nkhba.net\wordpress) in the text above and change it to ("alamhamasat.net") corresponding to the query
But MySql responds me that (0) rows were affected.
please help
|
|
|
|
|
Could you try the statement below?
select replace(option_value, 'wordpress', 'Hello World')
from wp_postmeta;
I are Troll
|
|
|
|
|
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
----------------------------
DELIMITER $$
CREATE PROCEDURE sp_srch(in empid varchar(10), empname varchar(45),mobileno varchar(15),bloodgroup varchar(5))
BEGIN
declare str varchar(255);
declare str1 varchar(20);
declare str2 varchar(20);
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 ");
end if;
if(empname!='') then
set str= Concat(str , "First_Name like '%" , empname ,"%'and ");
end if;
if(mobileno!='') then
set str= Concat(str , "Mobile_No like '%" , mobileno ,"%'and ");
end if;
if(bloodgroup!='') then
set str= Concat(str ,"Blood_Group like '" , bloodgroup ,"%' ");
end if;
set str1= (SUBSTR(str,-6));
set str2 = (SUBSTR(str,-4));
if(str1='where') then
set str= replace(str,'where','');
end if;
if(str2='and') then
set str= replace(str,' and','');
end if;
select str;
END $$
DELIMITER ;
|
|
|
|
|
EXEC (str)
or
sp_executesql str
But both are awful ways to implement a search query - and im betting you're passing textbox values directly into the SP - opening you up to sql injection attacks.
You should be doing it like the following, passing null for any params where you dont have a value (or not passing them at all, and taking the default, null)
CREATE PROCEDURE sp_srch(
@empid varchar(10) = NULL,
@empname varchar(45) = NULL,
@mobileno varchar(15) = NULL,
@bloodgroup varchar(5)) = NULL
BEGIN
select Emp_Id,First_Name,Last_Name,Gender,Mobile_No,Email_id,Blood_Group
from Employee
where (@empID IS NULL OR emp_id like '%' + @empID + '%')
and (@empName IS NULL OR first_name like '%' + @empName + '%')
and (@mobileno IS NULL OR mobile_no like '%' + @mobileno + '%')
and (@bloodgroup IS NULL OR blood_group like '%' + @bloodgroup+ '%')
|
|
|
|
|
J4amieC wrote: You should be doing it like the following, passing null for any params where you dont have a value (or not passing them at all, and taking the default, null)
Or passing 0 if is can be assumes that null and 0 are equivalent, this is then more fault tolerant
(ISNULL(@empID, 0) = 0 OR emp_id like '%' + @empID + '%')
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Or passing 0 if is can be assumes that null and 0 are equivalent, this is then more fault tolerant
All the parameters were varchar, so passing zero is impossible.
However your point is a valid one - for numeric optional input I would most certainly have the default as zero and check for nulls in the way you suggested.
|
|
|
|
|
J4amieC wrote: ll the parameters were varchar
Did not even look at that I assumed anything with an ID is numeric and this structure is almost rote for me.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
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.
SELECT
TABLE_NAME,
CASE
WHEN COUNT(TABLE_NAME) = 0 THEN
0
ELSE
IDENT_CURRENT(TABLE_NAME)
END
FROM
INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
AND
TABLE_TYPE = 'BASE TABLE'
GROUP BY
TABLE_NAME
|
|
|
|
|
SELECT CASE <br />
WHEN COUNT(TABLE_NAME) = 0 THEN 0<br />
WHEN COUNT(TABLE_NAME) > 0 THEN IDENT_CURRENT(TABLE_NAME)<br />
END<br />
FROM INFORMATION_SCHEMA.TABLES<br />
WHERE TABLE_TYPE = 'BASE TABLE'<br />
GROUP BY<br />
TABLE_NAME
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.
www.aktualiteti.com
|
|
|
|
|
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.
|
|
|
|
|
Dear all
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
Thank you
Lamis
|
|
|
|
|
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.
|
|
|
|
|
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
Field Name Data Type
Policy No Var
Office_Code Var
Vehicle_Reg_Number Var
Vehicle_Chas_Number var
Vehicle_Eng_Number Var
Date_Policy_Start_Date Date
Date_Policy_End_Date Date
Date_of_Cancellation Date
or
policyno var
policydata xml
|
|
|
|
|
Do NOT store your data using the XML data type, do the job properly and split the info into columns. The benifits are innumerable but include indexing and search/query ease of use. I hate XML.
Storing the history information is a business requirement, not a technical question.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
but dont you think i will change the column any time the xml schema change. that mean flexibility challange
|
|
|
|
|
HI,
i want to filter no.of rows where field1 contains symbol like %. How to select only those records contains like 20%,30%...etc.
************ S G KORE *******************
|
|
|
|
|
SELECT * FROM [Table] WHERE [Field] LIKE '%[%]'
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
