Database

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

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

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

<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 />

select replace(option_value, 'wordpress', 'Hello World')
  from wp_postmeta;

I are Troll

EXEC (str)

sp_executesql str

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)

Mycroft Holmes wrote:

Or passing 0 if is can be assumes that null and 0 are equivalent, this is then more fault tolerant

J4amieC wrote:

ll the parameters were varchar

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

SELECT * FROM [Table] WHERE [Field] LIKE '%[%]'

thatraja |Chennai|India|

---

Tips/Tricks|Brainbench certifications

Do what you want quickly because the Doomsday on 2012

eddieangel wrote:

There will always be a 1:1 relationship between the two tables.

I are Troll