Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Tuesday, May 29, 2012

Using CHARINDEX in SQL Query


CHARINDEX returns a starting position of a string which helps developer to split a data based on th erequirement. Below is an example of CHARINDEX usage:

DECLARE @AMT Varchar(50) = '$50K - $70K'
SELECT REPLACE(SUBSTRING('$50K - $70K',charindex('$', '$50K - $70K')+1, charindex('K', @AMT)),'K','') AS MinRange
,REPLACE(SUBSTRING('$50K - $70K',charindex('-', '$50K - $70K')+3, charindex('K', @AMT)),'K','') As MaxRange

The output is:
----------------------------------
MinRange      MaxRange
-----------------------------------
50                   70
-----------------------------------

1 comment:

  1. Nice post very useful also follow this link also has detail about CharIndex Function

    ReplyDelete