|
it will not work.
coz i'll never know the starts index and the length of string.
coz the data is randomize.
example :
i want to get "ASD654" and "CCCEEE4444" from the data :
"ASD654-100-CCCEEE4444-200-"
or to get "QWEQWEQ" and "JJJLLL" and "PPPBBMMNN" from :
"QWEQWEQ-2000-JJJLLL-20-PPPBBMMNN-600-"
any idea? 
is there any query for looping?
|
|
|
|
|
Well, you have to use - as split char, by writting your own function then you can get result as you need.
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
|
|
|
|
|
how to split char "-" in SQL ?
is there a function for looping in SQL ?
teach me please...
i'm begging...
pleaseee..... 
|
|
|
|
|
Use SUBSTRING function
SELECT SUBSTRING([ColumnName],12,10) FROM [Table]
|
|
|
|
|
but i'll never know the starts index and the length of string.
coz the data is randomize.
example :
i want to get "ASD654" and "CCCEEE4444" from the data :
"ASD654-100-CCCEEE4444-200-"
or to get "QWEQWEQ" and "JJJLLL" and "PPPBBMMNN" from :
"QWEQWEQ-2000-JJJLLL-20-PPPBBMMNN-600-"
any idea? 
|
|
|
|
|
For that you should write UDF, depends upon the random data you should define the things & write script for that.
|
|
|
|
|
i agree with blueboy where i have to use "-" as split char.
but the problem is i don't know how to split char using SQL Query.
and i think i need function for looping using SQL Query too.
can u help me pleaseee?
i'm begging....
|
|
|
|
|
|
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.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
